InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 11:49:03 GMT20http://www.sqlservercentral.com/Forums/Topic866064-1519-1.aspxIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Load_TestDB_From_Backup]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[usp_Load_TestDB_From_Backup]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate procedure [dbo].[usp_Load_TestDB_From_Backup]AS/* RESTORE the TEST version of the database from the latest backup This procedure uses a full backup for restoration and does NOT handle incremental or transaction log backups Mike Kober 2/16/2010*/ DECLARE @DBBackupFileName VARCHAR(500), --Local variable used to get the most recent backup path @SourceBackupPath varchar(500), --Where to look for the hard drive backups, filtering for this database name @RestoreName varchar(200), -- Database name that should be restored, I always use the DBNAME_TEST as the default @SourceDBname varchar(200), -- Logical DB Name used in SQL Server @SourceTXname varchar(200), -- Logical TX Name used in SQL Server @DestDBpath varchar(500), -- Full path and filename to where the MDF should be stored @DestTXpath varchar(500) -- Full path and filename to where the LDF should be stored SET @SourceBackupPath = 'T:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ChangeName\ChangeName_backup_%' SET @RestoreName = 'ChangeName_TEST' SET @SourceDBname = 'ChangeName' SET @SourceTXname = 'ChangeName_log' SET @DestDBpath = 'M:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\ChangeName_TEST_Data.MDF' SET @DestTXpath = 'L:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\ChangeName_TEST_Log.LDF' -- 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 = @SourceDBname) AND (BUMF.physical_device_name LIKE @SourceBackupPath) AND (RIGHT(BUMF.physical_device_name, 3) = 'BAK') ORDER BY BUS.backup_start_date DESC) -- Restore the files for Dbname_Test. RESTORE DATABASE @RestoreName FROM DISK = @DBBackupFileName WITH RECOVERY, MOVE @SourceDBname TO @DestDBpath, MOVE @SourceTXname TO @DestTXpath, REPLACEGOTue, 16 Feb 2010 06:44:52 GMTMichael Kober