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.
Who is Participating?
YveauConnect With a Mentor Commented:
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 ...
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 ...
smcdrcAuthor Commented:
I forgot to mention, these 2 DB's are on the same SQL server.  What would I change with your process, and how?

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.
Glad I could be of any help and thanks for the grade !
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.