Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automate Test Database Restoration


Automate Test Database Restoration

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

Group: General Forum Members
Points: 6 Visits: 213
Comments posted to this topic are about the item Automate Test Database Restoration
John Bonney
John Bonney
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
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."
Michael Kober
Michael Kober
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 213
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
rick-578359
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8704 Visits: 885
Thanks for the script.
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