SQL Server – Enabling backup CHECKSUM with Trace Flag 3023

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:

image

 

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:

image

 

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

About these ads
  1. January 17, 2013 at 5:25 am

    What is CHECKSUM ? and what is benefits if I Use CHECKSUM in backup ?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 288 other followers

%d bloggers like this: