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?


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

  • NOTE: If backup is restored successfully it does not always means backup is consistent, I have encountered a scenario in SQL Server 2008 r2 where corrupt backup was restored successfully but checkdb reported corruption. – Shanky Dec 12 '16 at 13:06
  • Agreed and that's why I have a TWO step process. 1). Restore and 2) CHECKDB – Scott Hodgin Dec 12 '16 at 13:07
  • Thanks! Very helpful to me. Gotta try this! thanks again! – Ricj Dec 12 '16 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.