How to conduct integrity test on SQL Server database backup file?

Question

I was assigned to check the integrity of database backups from sql server every two weeks. I'm new in this field so I don't know how to correctly do this task. If someone(hacker) modified the data inside the database backup, is it a part of the integrity of the file? Is there a way to check it?

Answer 1

There's an old saying...

The only way to verify a backup is good/usable is to restore it. REPEAT – you don’t know you have a good backup until you have restored it!

I would highly recommend checking your backups more often than every two weeks - Weekly, at a minimum and preferably more often than that.

At my shop, we have a 'play' server that we use to automate restores of the latest FULL/DIFF of the databases we deem 'important'. We use Windows Task Scheduler to kick off a bat file which has several SQLCMD steps. After successful restores, we run full DBCC CHECKDB WITH ALL_ERRORMSGS,NO_INFOMSGS and output the result to a txt file. Then we email the output txt files to the database group for evaluation.

This process tests TWO things

1. Can I restore my backups?
2. Is the data in the backup structurally intact (DBCC)

Here are some sample steps in our bat file

SQLCMD -S Localhost -Q "IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'<YourDatabase>') DROP DATABASE [<YourDatabase>]" -E -b -d master

SQLCMD -S Localhost -Q "EXEC SCUTILITY.dbo.RestoreProdForDBCC" -E -b -d SCUTILITY 

SQLCMD -S Localhost -Q "print 'Starting DBCC ' + convert(char,getdate(),121);dbcc checkdb ('<YourDatabase>') WITH ALL_ERRORMSGS,NO_INFOMSGS;print 'Completed DBCC ' + convert(char,getdate(),121)" -E -b -d master -o "F:\DBCC\dbcc_output.txt"

SQLCMD -S Localhost -Q "EXEC msdb.dbo.sp_send_dbmail @profile_name='SqlServerEmailProfile', @recipients='<DatabaseTeam>', @subject='DBCC has completed!', @file_attachments='F:\DBCC\dbcc_output.txt';" -E -b -d msdb

Check out these excellent posts for more information on Database Integrity Checking:

DBCC CHECKDB / Database Integrity

CHECKDB From Every Angle