Why would you do this?
As one of the responsibilities for Production DBA role, we would need to ensure that data is backup properly before the disaster strikes. And you wouldn’t know when that thing happens, be it the corrupted data pages, certain developers or even your DBA team members make any mistakes unintentionally/intentionally.
Backing up data is just half of the process, you might want to verify whether you can restore the DB using that backup data. It is fair to say that only when the restoration is successful without any error, the data that you back up is in good shape.
High level of backup process
The assumption is that there are 2 instances e.g, Production and DR/Testing instance with a shared folder in the network where it stores the precious backup data as per diagram below
The OS of DR/Testing instance is Windows 2012/2016.
Upload backup
I am using the backup script from Ola Hallengren to do the back up from the PROD instance to a shared folder in the network. There are tons of options for you to consider and customize the backup process. Typically, @Verify, @Compress, @CheckSum are the useful paramters and @Directory will be the shared folder.
As a common practice, a complete backup = Full + Differential + Log backup
FULL
EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = '\\ShareFolder\PRODinstance\Backup', @BackupType = 'FULL', @Compress = 'Y', @CheckSum = 'Y'
DIFFERENTIAL
EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = '\\ShareFolder\PRODinstance\Backup', @BackupType = 'DIFF', @Compress = 'Y', @CheckSum = 'Y'
LOG
EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = '\\ShareFolder\PRODinstance\Backup', @BackupType = 'LOG', @Compress = 'Y', @CheckSum = 'Y'
Download backup
You would need a simple script e.g. xcopy to copy the backups from shared folder to the server hosting the DR/Testing instance and running it under Task Scheduler
xcopy "\\SharedFolder\PRODinstance\Backup" "E:\MSSQL15.MSSQLSERVER\MSSQL\Backup"
For example, we got the following backup files with mixture of FULL + DIFF + LOG E:\MSSQL15.MSSQLSERVER\MSSQL\Backup\HaiDangSQL\ReportDatabase\FULL\HaiDangSQL_ReportDatabase_FULL_20220219_023000.bak E:\MSSQL15.MSSQLSERVER\MSSQL\Backup\HaiDangSQL\ReportDatabase\FULL\HaiDangSQL_ReportDatabase_DIFF_20220220_023000.bak E:\MSSQL15.MSSQLSERVER\MSSQL\Backup\HaiDangSQL\ReportDatabase\FULL\HaiDangSQL_ReportDatabase_DIFF_20220221_023000.bak E:\MSSQL15.MSSQLSERVER\MSSQL\Backup\HaiDangSQL\ReportDatabase\FULL\HaiDangSQL_ReportDatabase_LOG_20220221_033000.trn E:\MSSQL15.MSSQLSERVER\MSSQL\Backup\HaiDangSQL\ReportDatabase\FULL\HaiDangSQL_ReportDatabase_LOG_20220221_043000.trn E:\MSSQL15.MSSQLSERVER\MSSQL\Backup\HaiDangSQL\ReportDatabase\FULL\HaiDangSQL_ReportDatabase_LOG_20220221_053000.trn E:\MSSQL15.MSSQLSERVER\MSSQL\Backup\HaiDangSQL\ReportDatabase\FULL\HaiDangSQL_ReportDatabase_LOG_20220221_063000.trn E:\MSSQL15.MSSQLSERVER\MSSQL\Backup\HaiDangSQL\ReportDatabase\FULL\HaiDangSQL_ReportDatabase_LOG_20220221_073000.trn
There will be 3 steps to restore those backups automatically
- Create the restoration stored procedure Admin_RestoreDatabases_Daily under a datbased used by SQL Admin team. (e.g. AdminDB). There are 5 parameters with their name indicating the purpose
- @serverName
- @databaseName
- @backupPath
- @dataFilePath
- @logFilePath
- Create a SQL agent job with 2 steps calling to the stored procedure above to restore the database to ‘G:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data' (@dataFilePath) and ‘H:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data' (@logFilePath). The stored procedure will restore in the order of the FULL, followed by the latest DIFF then those LOG later than the latest DIFF.
Step 1:
GO
ALTER DATABASE ReportDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE AdminDB;
GO
exec dbo.Admin_RestoreDatabases_Daily 'HaiDangSQL','ReportDatabase',
'E:\MSSQL15.MSSQLSERVER\MSSQL\Backup\',
'G:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data\',
'H:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data\';
GO
ALTER DATABASE ReportDatabase SET MULTI_USER;
Step 2: Link the ‘apps_login’ user in this ReportDatabase to the existing ‘apps_login’ login in the DR/Testing instance. You might need to create this login before doing this step 2 if required.
USE ReportDatabase
exec sp_change_users_login 'Update_One', 'apps_login', 'apps_login';
GO
- Configure the SQL agent job schedule e.g. Daily @ 4.00 AM and alert in the event either of Step 1 or 2 above fails.
The task is completed!
Last but not least, a gentle reminder not to query the data in this DR/Testing instance unless you have it covered by a proper license.
Hoping you like this article and feel free to share any comments/feedbacks below.
Thank you for your time.