Automate Test Database Restoration

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 » Automate Test Database Restoration

Automate Test Database Restoration

Rate Topic

Display Mode

Topic Options

Author

Message

Michael Kober

Posted Saturday, March 28, 2009 6:14 AM

Forum Newbie

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

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

Post #685547

John Bonney

Posted Thursday, April 23, 2009 8:19 AM

Valued Member

Group: General Forum Members
Last Login: Monday, May 16, 2011 7:47 AM
Points: 58, Visits: 77

Nice script.

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

Post #703240

Michael Kober

Posted Thursday, April 23, 2009 9:28 AM

Forum Newbie

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

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.

Post #703327

rick-578359

Posted Thursday, December 03, 2009 9:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, August 30, 2011 7:31 AM
Points: 4, Visits: 15

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

Post #828287

« Prev Topic | Next Topic »

Permissions