Version 2 Posted

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mike Kober » Version 2 Posted

Version 2 Posted

Rate Topic

Display Mode

Topic Options

Author

Message

Michael Kober

Posted Tuesday, February 16, 2010 6:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:16 AM
Points: 6, Visits: 138

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

Post #866064

« Prev Topic | Next Topic »

Permissions