You can use WITH CHECKSUM option to perform checksum when backup is created. When used this verifies each page for checksum and torn page.

You can use it in a BACKUP command as below:

BACKUP DATABASE [SqlAndMe]

TO DISK = N'C:\SqlAndMe.bak'

WITH CHECKSUM

GO

Once the backup is created you can check the header to verify if backup was created with checksum.

RESTORE HEADERONLY

FROM DISK = N'C:\SqlAndMe.bak'

GO

Result Set:

However, if you do not have control over underlying T-SQL for backup then you can enable backup checksums using Trace Flag 3023. When this Trace Flag is turned on WITH CHECKSUM option is applied automatically for a backup.

In the following example I have run a backup before enabling Trace Flag 3023, and after enabling Trace Flag 3023 to see it’s behavior:

– Run without Trace Flag 3023

BACKUP DATABASE [SqlAndMe]

TO DISK = N'C:\SqlAndMe.bak'

GO

DBCC TRACEON(3023)

GO

– Run after enabling Trace Flag 3023

BACKUP DATABASE [SqlAndMe]

TO DISK = N'C:\SqlAndMe.bak'

GO

DBCC TRACEOFF(3023)

GO

Result Set:

Processed 240 pages for database 'SqlAndMe', file 'SqlAndMe' on file 1.

Processed 1 pages for database 'SqlAndMe', file 'SqlAndMe_Log' on file 1.

BACKUP DATABASE successfully processed 241 pages in 0.213 seconds (8.839 MB/sec).

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Processed 240 pages for database 'SqlAndMe', file 'SqlAndMe' on file 2.

Processed 1 pages for database 'SqlAndMe', file 'SqlAndMe_Log' on file 2.

BACKUP DATABASE successfully processed 241 pages in 0.376 seconds (5.007 MB/sec).

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now, we can look at the header of the backup file to verify if the backup has been created with checksum:

RESTORE HEADERONLY

FROM DISK = N'C:\SqlAndMe.bak'

GO

Result Set:

As we can see from the output, First backup was created without checksums and second backup (after enabling Trace Flag 3023) is created with checksums without explicitly specifying WITH CHECKSUM option.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Filed under: Backup & Recovery, SQL Configuration, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Undocumented Functions