SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Version 2 Posted


Version 2 Posted

Author
Message
Michael Kober
Michael Kober
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 213
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search