473,320 Members | 1,969 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Restore Database daily without SP's

Hello all,

I have the following requirement (SQL 2000, SP2)

1. Need to restore production database(A) to another database (B) on
another server (No direct connection)
2. In the restored back-up(B) SP codes should not be visible (even to
sa) or all SP's can be deleted .

3. This needs to be carried out daily

4. New tables may be added frequently to A

5. This job should be scheduled

6. I can overwrite the entire database (B)

7. I can not encrypt SP in the original database (A)
I thought of Snap-shot replication first. But when new tables are
added , I need to add new tables manually to publication .

Can some one suggest most reliable and implementable method ?

Thanks
Srinivas
Jul 20 '05 #1
2 2902

"M A Srinivas" <ma***@vsnl.com> wrote in message
news:f7**************************@posting.google.c om...
Hello all,

I have the following requirement (SQL 2000, SP2)

1. Need to restore production database(A) to another database (B) on
another server (No direct connection)
2. In the restored back-up(B) SP codes should not be visible (even to
sa) or all SP's can be deleted .

Hmm, a normal Backup/Restore won't do this (since it'll restore the stored
procs.)

You're probably better off writing a DTS package.

3. This needs to be carried out daily

4. New tables may be added frequently to A
That might be difficult to handle with a DTS package automatically, but if
you make sure you add them manually you're all set.

Another option would be to have a SECOND DB and put the stored procs in
there and then just qualify all stored procs by prepending the name of the
second DB.

So, myProc_foo in the first database would be moved to the 2nd one and now
called SecondDB..myProc_foo.

A little more complicated, but doable.


5. This job should be scheduled

6. I can overwrite the entire database (B)

7. I can not encrypt SP in the original database (A)
I thought of Snap-shot replication first. But when new tables are
added , I need to add new tables manually to publication .

Can some one suggest most reliable and implementable method ?

Thanks
Srinivas

Jul 20 '05 #2
Greg D. Moore (Strider) (mo*****@greenms.com) writes:
Hmm, a normal Backup/Restore won't do this (since it'll restore the stored
procs.)
...
Another option would be to have a SECOND DB and put the stored procs in
there and then just qualify all stored procs by prepending the name of the
second DB.


Aargh! Never hard-code DB-names! You get all sorts of trouble if you
need to set up a test environment on the same server.

However, a second DB was my thought to. First backup original DB. Restore
locally into a second database. Drop all stored procedures. Backup the
second database. Ship and restore on the second server.

If the disk space and time is available, this is probably the simplest
way.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Tim Morrison | last post by:
MSDE2000 I have an application in which I am running a TSQL command of BACKUP DATABASE and RESTORE DATABASE for the backup and restore commands for my application. For testing purposes, i did the...
0
by: xo55ox | last post by:
Hi, I have been trying to set up an automated restore process from prod to backup server. First, I schedule the full database backup nightly, transfer the backup file and restore it to the...
0
by: barbara | last post by:
I am using this procedure from net for getting the restore script. It lists the latest full backup file name and logs after that point. Is there any way to modify this script to take either date...
4
by: ScottH | last post by:
I am running 7.2 with fixpack 10a under W2K. I got a backup from someone else in an attempt to debug a data issue. The other person created the database with UTF-8 (codepage 1208), so I created a...
5
by: HSP | last post by:
hi. i need to restore an old database. The db was backed up using a DLT drive, using 2 volumes. The content for the tapes was copied to file onto Solaris machine using rsh and dd (for backup...
0
by: richlittle | last post by:
I am using mysql administrator to perform scheduled daily backups of my employee database. What guarantee do I have after doing a restore that everything has restored correctly? My boss want...
4
by: africantract | last post by:
Hi There, We have an issue on our db2 restores. We do database dumps every night, and restore them to development servers every morning. This works fine with no errors. However if we backup those...
1
by: ILPTAB | last post by:
Hello. Can somebody help me out here? I have a web server sitting on a DMZ that has DB2 UDB version 8 installed running on it. I would like this server to be completely independent of our...
5
by: Seguros Catatumbo | last post by:
Hi, i am using sql server 2000, and i make daily backups. I want to restore my backup to a test msde database i have. I don't know how i did it last time. -The database name is the same: "web"...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.