Automate Test Database Restoration

Question

Automate Test Database Restoration

Michael Kober

Valued Member

Points: 66
More actions
March 28, 2009 at 6:14 am

#384239

Comments posted to this topic are about the item Automate Test Database Restoration

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Bonney SSChasing Mays Points: 602 More actions · Answer 1

Nice script.

I opted to include it as part of my nightly production backup maintenance plan so that the test database is always "fresh."

Michael Kober Valued Member Points: 66 More actions · Answer 2

That's what I use for all of our DBs so we have production and test in sync daily.

I had tried using this for a development instance on a different server but found it would time out due to physical size issues when being transferred across the network. So I create the dev instance manually since I don't want it to get wiped out every night.

rick-578359 SSC Enthusiast Points: 192 More actions · Answer 3

--a More agnostic version: Variables for all elements

--Great code thanks

USE [master]

GO

create procedure [dbo].[usp_Load_SandboxDB_From_Backup]

AS

/*

RESTORE the Sandbox version of the database from the latest backup

*/

DECLARE @DBBackupFileName VARCHAR(500)

Declare @BackupDrive varchar(100)

Declare @BackupFilePrefix varchar(30)

Declare @SourceDB varchar(30)

Declare @sourceLog varchar(30)

Declare @Datastoragepath varchar(200)

declare @RestoreDB varchar(30)

declare @RestoreLog varchar(30)

declare @BackupExt varchar(10)

DECLARE @DBSource varchar(20)

DECLARE @DBDestination varchar(20)

declare @FullRestoreDB as varchar(200)

declare @FullRestoreLog as varchar(200)

set @DBSource = 'CEI'

set @DBDestination = 'SAND'

set @BackupDrive = 'D:\SQLBACKUP\CEI\'

set @BackupFilePrefix = 'CEI_backup_%'

set @BackupExt = 'BAK'

set @SourceDB = 'GPSCEIDat.mdf'

set @sourceLog = 'GPSCEILog.ldf'

set @Datastoragepath = 'D:\Program Files\Microsoft SQL Server\MSSQL10.MBS\MSSQL\DATA\'

set @RestoreDB = 'GPSSANDDat.MDF'

set @RestoreLog = 'GPSSANDLog.LDF'

set @FullRestoreDB = @Datastoragepath + @RestoreDB

set @FullRestoreLog = @Datastoragepath + @RestoreLog

-- 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 = @DBSource)

AND (BUMF.physical_device_name LIKE @BackupDrive + @BackupFilePrefix)

AND (RIGHT(BUMF.physical_device_name, 3) = @BackupExt)

ORDER BY

BUS.backup_start_date DESC)

print 'DBBackupfilename:=' + @DBBackupFileName

Print 'Destination := ' + @DBDestination

print 'Sourcedb := ' + @Sourcedb

print 'SourceLog := ' + @SourceLog

print 'FullRestoredb := ' + @fullrestoredb

print 'FullRestoreLog := ' + @FullRestorelog

-- Restore the files for sandbox.

--RESTORE FILELISTONLY

--FROM DISK = @DBBackupFileName

RESTORE DATABASE @DBDestination

FROM DISK = @DBBackupFileName

WITH RECOVERY,

MOVE @SourceDB TO @FullRestoreDB,

MOVE @sourceLog TO @FullRestoreLog,

REPLACE

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 4

akljfhnlaflkj

SSC Guru

Points: 76202

May 2, 2016 at 3:38 pm

#1876056

Thanks for the script.