Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Version 2 Posted Expand / Collapse
Author
Message
Posted Tuesday, February 16, 2010 6:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 22, 2014 9:08 AM
Points: 6, Visits: 178
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 »

Add to briefcase

Permissions Expand / Collapse