Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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: 3544 | Views in the last 30 days: 34
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


regarding restoring backup file taken from 32-bit operating system in 64-bit operating system

regarding restoring backup file taken from 32-bit operating system in 64-bit operating system


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones