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.