How to auto restore Production DB to Development

Posted on 2007-10-03
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
    LVL 18

    Expert Comment

    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 ...
    LVL 2

    Author Comment

    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

    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

    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

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

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    Creating and Managing Databases with phpMyAdmin in cPanel.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now