How to backup sql server database with checksum option

Introduction

If you compare Oracle database and MS SQL Server databases, there are some wins for Oracle and some for MS SQL Server. In this blog post, I will talk about a backup feature with Oracle as the winner.

Oracle block checking during RMAN backups

By default, an Oracle RMAN database backup computes the checksum for each block and stores it in the backup, even if the DB_BLOCK_CHECKSUM command is set to TYPICAL. DB_BLOCK_CHECKSUM initialization parameter applies to data files in the database and not applicable to backups. 

Therefore one way of recognizing block media corruptions in an Oracle database is when Oracle RMAN database backup terminates with errors after it fails to perform a checksum for those corrupted blocks. So an Oracle DBA need not monitor for block media corruptions separately because the database backup (if the RMAN method is used) command itself acts as such a validation tool inherently.

MS SQL Server page (block) checking during backups

The corresponding term in MS SQL Server for datafile blocks is a page.  In SQL Server, most database administrators create the backup database task through maintenance plans. But one cannot rely on these maintenance plans for page (block) checksum and torn-page detection during backups. Even if the "Verify backup integrity"  option is enabled as part of the backup database task in a maintenance plan, it does nothing more than checking whether the backup media is readable without having to perform an actual restore. So it is superficial in nature.

Therefore, it is strongly recommended that MS SQL Server database administrators create separate T-SQL scripts or stored procedures to explicitly backup databases using the with checksum option. 

For example, a database called AdventureWorks can be backed up using the below command.

backup database AdventureWorks  to disk = 'D:\backups\AdventureWorks_01032015.bak' with NOINIT,CHECKSUM;

Additionally, in the same T-SQL script or stored procedure, verify backup integrity using the with checksum option. For example,

restore verifyonly from disk = 'D:\backups\AdventureWorks_01032015.bak' with CHECKSUM;

SQL Server will check every page in the backup file by reading and recomputing the page checksum and then compares it to the recorded page checksum in the backup file to ensure integrity.

Conclusion

It is recommended that MS SQL Server DBAs create a separate T-SQL script or a stored procedure to backup all databases using the with checksum option. This blog post is validated against Oracle 11g R2 and 10g R2 databases and MS SQL Server 2008 and 2008 R2 databases. Please note the extra time taken for a restore verifyonly with checksum command.  Another observation that may be noted is that a backup without a checksum and a backup with checksum  takes about the same time.