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
- Can I restore my backups?
- 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: