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.
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)
BACKUP DATABASE [OLTP_DB] TO DISK = N'I:BackupDB_BackupStrip2.bak',
DISK = N'I:BackupDB_BackupStrip1.bak'
WITH COPY_ONLY, RETAINDAYS = 21, NOFORMAT, NOINIT,
NAME = @BackupName,
SKIP, REWIND, NOUNLOAD, STATS = 10
xcopy "I:BackupDB_BackupStrip1.bak" "10.10.10.2Backup Test Automation" /y /j
xcopy "I:BackupDB_BackupStrip2.bak" "10.10.10.2Backup 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:-
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:-
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;
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;
Make sure every Agent Job has one or more notification operator to notify, in case of any failure. [/info]