SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Automate Test Database Restoration

By Mike Kober,

A standard development practice is to have at least 3 databases for Production, Development, and Test. I run full backups daily since the production systems aren't used during the night and full backups run within an adequate time period. This script uses the daily backup and restores a test version of the production backup, overwriting the existing test system.

Change your source database (PES here) to your system, change the paths to your image location. I use the hard drive to store the bak files and simply save them with the tape systems. This way I can automate the restorations much faster than worrying about the tape systems. Lookup your data and log names from SQL Manager and replace the MOVE statements in the restore section.

I had to add the 'BAK' physical device name since this script would also pickup the transaction log backups which isn't what I wanted. You could modify this to include the TX backups, but my full backup is clean at this point so I don't need it.

At the start of every day I have two identical systems, Production and Test. If no one uses Test and some user messes things up in Production, I can compare the two systems to see what they did. If someone did use Test, or more often I'm using it and want to reset the data back to the start, I just run the proc and instantly I have my database reset to the start of the day. Quite nice!

Total article views: 3677 | Views in the last 30 days: 2
Related Articles

Automate Your Backup and Restore Tasks

A new article that shows how you can automate a basic function that many environments need: the back...


Production Server DB backup Restored on Development Server Backup

Production Server DB backup Restored on Development Server Backup


Restoring system databases

System databases have been restored to new server but user DBs 'suspect'


Restore with no backup

Database dropped, no backup. Need to restore


Automatic database restore

Often times database administrators were asked to restore production database backup to UAT or devel...