Link to home
Start Free TrialLog in
Avatar of smcdrc
smcdrc

asked on

How to auto restore Production DB to Development

I have a production and development SQL Server 2000 database.  I want to automatically restore the production db to the Development DB each night, or some schedule.  How can I do this.  

I am currently making nightly backups of the production database.
Avatar of Yveau
Yveau
Flag of Netherlands image

On the dev server, predict the time that the database will be backed up by the production server.
First step in the job is to copy the .bak file to the dev server.
Second step will be using the restore command to restore the database:

restore database <yourDB>
from disk = 'X:\Path\To\Local\Copy.bak'
with move 'logical_file_name' TO 'operating_system_file_name'

Then you almost certain end up with a bunch of orphaned users.
Create a small SQL Script that will drop all users from the prod db and recreate the users to the according logins on the dev server. Run the script as the third step in this job.

... done !

hope this helps ...
Avatar of smcdrc
smcdrc

ASKER

I forgot to mention, these 2 DB's are on the same SQL server.  What would I change with your process, and how?

Thanks,
I did this as a SQL job to move production to report.

I had a job that performed the production backup.

I estimated the time it would take and then added a couple of hours. I had another job that had several steps.
The first step dropped every user from the report database (it was a cursor that kill each user).
Second step, the restore.
Third step, because this was a report database, I truncated the log.
ASKER CERTIFIED SOLUTION
Avatar of Yveau
Yveau
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Glad I could be of any help and thanks for the grade !