Version 2 Posted

• Michael Kober

  Valued Member

  Points: 66

  More actions

  February 16, 2010 at 6:44 am

  #216656

  IF 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]

  GO

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  create 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,

  REPLACE

  GO