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

Tags

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (7)

You rated this post out of 5. Change rating

Related content

Technical Article

Tuning the Performance of Backup Compression in SQL Server 2008

ckup compression is a new feature in SQL Server 2008 that can help provide smaller sized backups and reduce backup time. This document provides guidance related to tuning options for backup performance. All of the information and test results presented here were done specifically by using the backup compression feature of SQL Server 2008; however, they apply broadly to any backup scenario whether backup compression is used or not

2008-05-12

2,214 reads