DR Test Backup and restore drill test

Overview:

There was requirement from business to test live server backup into DR server every month / week, not a completed DR. It was about 30+ databases with total of 500 GB.

Steps:

Add net use command in sp_DatabaseRestore stored procedure
Share backup folder path in source server “\”
Run the find the recent backup script on source server
Script out and run it on target server where you have created sp_DatabaseRestore
Run the restore command in SSMS or SQL agent job.
Test Restore with execute N parameter and make sure everything is correct and run with Y.

 

I have started using Ola Hallengren’s maintenance script and searched for restore script found Brent Ozar’s website.

 

Here are the steps to test:

Use Ola Hallengren’s maintenance script

Create Brent Ozar’s First Responder Kit.

Modify the SP sp_DatabaseRestore and add “exec xp_cmdshell ‘net use \x.x.x.x\Database_Backup /USER:domain\user password'” to allow permission in the beginning of the SP to access the backup share from DR server.

Enable xp_cmdshell

sp_configure 'show advan',1;reconfigure
sp_configure 'xp_cmdshell',1;reconfigure

Share backup folder path in source server “\” (OR) If you cannot share it, copy and paste the backup target and adjust the script based on the path.

Run this find the recent backup on source server

WITH LastBackUp AS
(
SELECT bs.database_name,
bs.backup_size,
bs.backup_start_date,
--replace (bmf.physical_device_name,'G:','\\x.x.x.x') as physical_device_name,
replace(LEFT(bmf.physical_device_name,CHARINDEX('\FULL\',bmf.physical_device_name,1) + LEN('FULL\')),'G:','\\x.x.x.x') as physical_device_name,
Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
FROM msdb.dbo.backupmediafamily bmf
JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
WHERE bs.[type] = 'D'
AND bs.is_copy_only = 0
)
--EXEC dbo.sp_DatabaseRestore @Database = 'dbPainTrax_2',@BackupPathFull = '\\x.x.x.x\Dropbox\Database_Backup\',@ContinueLogs = 0,@RunRecovery = 0;
SELECT 
'EXEC dbo.sp_DatabaseRestore @Database ='''+sd.name+''',@BackupPathFull ='''+physical_device_name+''',@ContinueLogs = 0,@RunRecovery = 1
,@MoveFiles=1,@MoveDataDrive=''E:\SQL_DataBase\'',@MoveLogDrive=''F:\SQLDatabase\'',@Execute = ''Y'';' as cmd,
sd.name AS [Database],
CAST(backup_size / 1048576 AS DECIMAL(10, 2) ) AS [BackupSizeMB],
backup_start_date AS [Last Full DB Backup Date],
physical_device_name AS [Backup File Location]
FROM sys.databases AS sd
LEFT JOIN LastBackUp AS lb
ON sd.name = lb.database_name
AND Position = 1
where database_name not in ('master','model','msdb','tempdb')
and database_name not like 'dba%'
ORDER BY [backup_start_date];



/*
EXEC dbo.sp_DatabaseRestore @Database ='db_PharmaTrax_Test_New',
@BackupPathFull ='\\x.x.x.x\Dropbox\TechSupport\Database_Backup_Daily\WIN-VMC-SQL001$SQLEXPRESS\db_PharmaTrax_Test_New\FULL\'
,@RunRecovery = 1,@ContinueLogs = 0,@Execute = 'N',@MoveFiles=1,@MoveDataDrive='E:\SQLDataBase\',@MoveLogDrive='F:\SQLDatabase\';
*/

Script out and run it on target server where you have created sp_DatabaseRestore

Run the restore command in SSMS or SQL agent job.

Test Restore with execute N parameter and make sure everything is correct and run with Y.

--/*

-- Enable xp_cmdshell
-- Create SP sp_DatabaseRestore https://www.brentozar.com/archive/2017/03/databaserestore-open-source-database-restore-stored-procedure/
-- Alter SP with net use to allow permission to the prod backup share
-- Run recent-backup-finding.sql to get the data, copy and paste the CMD column into new query windwos and run
ex:
EXEC dbo.sp_DatabaseRestore @Database ='DBA_Trace_Load_14_feb_2022',@BackupPathFull ='\\x.x.x.x\Database_Backup_Daily\xx-SQL001$SQLEXPRESS\DBA_Trace_Load_14_feb_2022\FULL\',@ContinueLogs = 0,@RunRecovery = 1;
--*/

sp_configure 'show advan',1;reconfigure

sp_configure 'xp_cmdshell',1;reconfigure


exec xp_cmdshell 'net use \\x.x.x.x\Dropbox\Database_Backup  /USER:x.x.x.x\Administrator password'
exec xp_cmdshell 'net use \\x.x.x.x\Dropbox\Database_Backup  /DELETE /y'

--Run with -- @Execute = 'N' and find out your restore is correct and Data & log path are correct

EXEC dbo.sp_DatabaseRestore @Database ='dbname',
@BackupPathFull ='\\x.x.x.x\Dropbox\TechSupport\Database_Backup_Daily\WIN-VMC-SQL001$SQLEXPRESS\dbname\FULL\'
,@RunRecovery = 1,@ContinueLogs = 0,@Execute = 'N',@MoveFiles=1,@MoveDataDrive='E:\SQL_DataBase\',@MoveLogDrive='F:\SQL_Database\';

 

Complete script available here in my GitHub

Hope this would help someone.