How to auto restore Production DB to Development

Posted on 2007-10-03
Medium Priority
Last Modified: 2008-01-09
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.
Question by:smcdrc
  • 3
LVL 18

Expert Comment

ID: 20009883
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 ...

Author Comment

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

LVL 27

Expert Comment

ID: 20010029
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.
LVL 18

Accepted Solution

Yveau earned 2000 total points
ID: 20010052
OK, so that is not a problem.

Use the exact statement as I posted. Let me clear this out for you based on a speaking example:

restore database DevDB
from disk = 'X:\Path\To\Local\ProdDB.bak'
with move 'ProdDB' to 'X:\Path\To\DevDB.mdf'
,     move 'ProdDB_log' to 'X:\Path\To\DevDB_log.ldf'

This should restore the ProdDB database to the same server under the name of DevDB. ANd the story on the orphaned users can be skipped as you are on the same server. ... and you even don't have to copy the file ! So just restoring and you're done !

Hope this helps ...
LVL 18

Expert Comment

ID: 20051851
Glad I could be of any help and thanks for the grade !

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question