Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Automate Test Database Restoration Expand / Collapse
Author
Message
Posted Saturday, March 28, 2009 6:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 22, 2014 9:08 AM
Points: 6, Visits: 178
Comments posted to this topic are about the item Automate Test Database Restoration
Post #685547
Posted Thursday, April 23, 2009 8:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Thursday, April 23, 2009 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 22, 2014 9:08 AM
Points: 6, Visits: 178
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
Posted Thursday, December 3, 2009 9:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse