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.
I am currently making nightly backups of the production database.
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,
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad I could be of any help and thanks for the grade !
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_nam
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 ...