Database Backup Verification Policy Automation

Taking regular database backup is important and most of DBAs are assigned to perform this task, but making sure the backup is restorable and entire database is accessible once restored is very important. Basically every database should have its own ‘Backup & Restore’ policy which is designed based on the RTO, RPO and backup strategy.

SQL Server provides a command called 'Restore VerifyOnly', verifying a backup is a useful practice. Verifying a backup checks that the backup is intact physically, to ensure that all the files in the backup are readable and can be restored, and that you can restore your backup in the event you need to use it. It is important to understand that verifying a backup does not verify the structure of the data on the backup. However, if the backup was created using WITH CHECKSUMS, verifying the backup using WITH CHECKSUMS can provide a good indication of the reliability of the data on the backup.

Sometimes happened that database backup is corrupted but seems not corrupted, for example the checksum of the backup file after corruption is same as prior corruption. In this case SQL Server restores the database but you are not able to access the entire database. Therefore, we do need to restore the latest database to make sure the database is accessible.

Let’s imagine, we do have one production server and UAT server. In this scenario we will take full database backup from production server, copy, restore database, test database and send notification email to DBA.

backup policy 1

To implement such technique, we need to have shared directory in SQLUAT server to copy the recent database backup file(s) into and few SQL Agent Jobs to perform the automations.

Database Backup and XCopy Automation

At this point, we need to create an Agent Job on SQLPROD to perform the database full backup and XCopy command-line to copy the database backup files into the SQLUAT server.

Full Backup Script:-

[codesyntax lang=”tsql”]

Declare @BackupName nvarchar(300);

Select @BackupName = N'OLTP_DB Full Database Backup on ' + Convert(Nvarchar,SysDateTime(),101);

IF ([master].sys.fn_hadr_backup_is_preferred_replica('OLTP_DB') = 1)
    BACKUP DATABASE [OLTP_DB] TO  	DISK = N'I:BackupDB_BackupStrip2.bak',  
					DISK = N'I:BackupDB_BackupStrip1.bak' 
	NAME = @BackupName, 


XCopy Script:-

[codesyntax lang=”powershell”]

xcopy "I:BackupDB_BackupStrip1.bak" " Test Automation" /y /j
xcopy "I:BackupDB_BackupStrip2.bak" " Test Automation" /y /j


[plain]Database backup and XCopy scripts must run prior ‘Database Restore’ Agent job, recommended One (1) hour after.[/plain]

Database Restore and Data Validation Automation

At this point, the agent job runs to restore the recent database backup into the SQLUAT server and retrieve 20% data from every single table and views to make sure entire database is accessible from recent full backup.

Database Restore Script:-

[codesyntax lang=”tsql”]

Restore Database DB_Backup_Test from 
	Disk = 'I:Backup Test AutomationDB_BackupStrip1.bak', 
	Disk = 'I:Backup Test AutomationDB_BackupStrip2.bak'  with replace;


Data Validation Script:-

[codesyntax lang=”tsql”]

use DB_Backup_Test;

declare @errorcount int;
Declare @ObjName nvarchar(200);
Declare CUR Cursor for select '[' + '].[' +']' from sys.tables T Inner Join sys.schemas S On S.schema_id = T.schema_id;

Open CUR;

Fetch Next From CUR into @ObjName;

While (@@FETCH_STATUS  -1)
	exec ('Select * from '+@ObjName + ' TableSample(20 Percent)');
	Set @errorcount += @@ERROR;
	Fetch Next From CUR into @ObjName;

Close CUR;
Deallocate CUR;



Make sure every Agent Job has one or more notification operator to notify, in case of any failure. [/info]

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles


Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan