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