G’Day,

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

RESTORE HEADERONLY FROM DISK = 'C:\Martin\model.bak';

Yep, the “HasBackupChecksums” is not on.

I could have run

RESTORE VERIFYONLY FROM DISK = 'C:\Martin\model.bak'
WITH
STATS = 5,
CHECKSUM;

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.

Cheers

Martin.

https://support.microsoft.com/en-us/kb/2656988