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

• John Bonney

  SSChasing Mays

  Points: 602

  More actions

  April 23, 2009 at 8:19 am

  #982943

  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

  April 23, 2009 at 9:28 am

  #983020

  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

  December 3, 2009 at 9:11 am

  #1087192

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

  May 2, 2016 at 3:38 pm

  #1876056

  Thanks for the script.