Technical Article

Automate Test Database Restoration

,

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!

CREATE procedure [dbo].[usp_Load_TestDB_From_Backup]
AS
/*
RESTORE the TEST version of the database from the latest backup
*/
DECLARE @DBBackupFileName VARCHAR(500)

-- First Get the last saved backup from disk.

SELECT 

@DBBackupFileName = 
(
SELECT TOP (1) 
BUMF.physical_device_name
FROM         
msdb.dbo.backupmediafamily AS BUMF 
INNER JOIN msdb.dbo.backupmediaset AS BUMS ON BUMF.media_set_id = BUMS.media_set_id 
INNER JOIN msdb.dbo.backupfile AS BUF 
INNER JOIN msdb.dbo.backupset AS BUS ON BUF.backup_set_id = BUS.backup_set_id ON BUMS.media_set_id = BUS.media_set_id
WHERE     
(BUS.database_name = 'PES') 
AND (BUMF.physical_device_name LIKE 'U:\Backup\PES\PES_backup_%') 
AND (RIGHT(BUMF.physical_device_name, 3) = 'BAK')
ORDER BY 
BUS.backup_start_date DESC)

--SELECT @DBBackupFileName

-- Restore the files for pubs_Test.
RESTORE DATABASE PES_TEST
FROM DISK = @DBBackupFileName
WITH RECOVERY,
MOVE 'CCO_PROVIDER' TO 'K:\SQLData\PES_TEST_Data.MDF',
MOVE 'CCO_PROVIDER_log' TO 'L:\SQLLog\PES_TEST_Log.LDF',
REPLACE

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (7)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (7)

You rated this post out of 5. Change rating

Related content

External Article

Selecting the database recovery model to ensure proper backups

One of the first things that should be done when managing SQL Server is to setup an appropriate backup plan in order to minimize any data loss in the event of a failure. Along with setting up a backup plan there are certain database configurations that need to be setup to ensure you are able to backup databases correctly. In this tip we will look at the different recovery models that SQL Server offers and how to choose a recovery model for your database.

2008-05-30

4,151 reads