How to conduct integrity test on SQL Server database backup file?
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'&lt;YourDatabase&gt;') DROP DATABASE [&lt;YourDatabase&gt;]" -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 ('&lt;YourDatabase&gt;') 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='&lt;DatabaseTeam&gt;', @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