Database Backup Verification Policy Automation

The estimated reading time for this post is 3 minutes

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

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)
BEGIN
    BACKUP DATABASE [OLTP_DB] TO  	DISK = N'I:\Backup\DB_BackupStrip2.bak',  
					DISK = N'I:\Backup\DB_BackupStrip1.bak' 
	WITH  COPY_ONLY,  RETAINDAYS = 21, NOFORMAT, NOINIT,  
	NAME = @BackupName, 
	SKIP, REWIND, NOUNLOAD,  STATS = 10
END

XCopy Script:-

xcopy "I:\Backup\DB_BackupStrip1.bak" "\\10.10.10.2\Backup Test Automation" /y /j
xcopy "I:\Backup\DB_BackupStrip2.bak" "\\10.10.10.2\Backup Test Automation" /y /j

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

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

RESTORE DATABASE DB_Backup_Test FROM 
	DISK = 'I:\Backup Test Automation\DB_BackupStrip1.bak', 
	DISK = 'I:\Backup Test Automation\DB_BackupStrip2.bak'  WITH REPLACE;

Data Validation Script:-

USE DB_Backup_Test;
go
 
 
DECLARE @errorcount INT;
DECLARE @ObjName NVARCHAR(200);
DECLARE CUR CURSOR FOR SELECT '['+S.name + '].['+T.name +']' 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)
BEGIN
	EXEC ('Select * from '+@ObjName + ' TableSample(20 Percent)');
	SET @errorcount += @@ERROR;
	FETCH NEXT FROM CUR INTO @ObjName;
END
 
CLOSE CUR;
DEALLOCATE CUR;

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

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz