TSQL Tuesday #68 – backup checksum default



Well it seems like TSQL Tuesday #68 is providing me with a real good reason to get back into my blogging again. It been a while – due to various life events – but I think it’s time to dust off my virtual pen and start a (hopefully) flourishing blogging career.

This months TSQL Tuesday is hosted by Andy Yun ( b | t ) and the subject is “SQL Server Defaults”

Well, like a few organisations, the one that employs me has not yet upgraded to SQL Server 2014. The plus side of that is that I’m now getting to evaluate the latest CTP of SQL Server 2016 with a view to upgrading as soon s it hits RTM.

So, as soon as I get the latest CTP installed I run

SELECT * FROM [sys].[configurations] [C]

and I see an option called “backup checksum default”

oh, I think! that must have just been introduced.

But, it came along in SQL Server 2014 – it’s just I’d forgot that.

So what’s all this got to do with defaults? Well, the default for this is to not have checksums on.

I’d suggest that this is one default that I’ll be setting to ON when I finally get to install 2016.

Over the last few years I’ve always got into the habit of adding the CHECKUM Command to my backups.

So, I just decided to run a backup command just to be 100% sure that it wasn’t on.

BACKUP DATABASE [model] TO DISK = 'C:\Martin\model.bak';

and just to check


Yep, the “HasBackupChecksums” is not on.

I could have run

STATS = 5,

but I’d have got the error

Msg 3187, Level 16, State 1, Line 11
RESTORE WITH CHECKSUM cannot be specified because the backup set does not contain checksum information.
Msg 3013, Level 16, State 1, Line 11
VERIFY DATABASE is terminating abnormally.

so, there you are.

The “backup checksum default”, introduced in SQL Server 2014 is off by default. Should it be ON by default.

Well, it sure will be on my nice new shiny 2016 servers when I get to install them.

Have a great day.












About Martin Catherall

Martin Catherall is Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.