Enabling Backup Checksum by default using T3023 and sp_configure

Question

Enabling Backup Checksum by default using T3023 and sp_configure

Santhoshkumar KB

SSCertifiable

Points: 5066
More actions
April 5, 2017 at 7:10 am

#401899

Hi,
Trace flag 3023 was added as a startup parameter to enable the backup checksum by default.
When I look at the sys.configurations, the value is still 0.
However, the field has_backup_checksums shows 1 when a database is backed up.
select * from sys.configurations where name like 'back%'
Question:
If I configure using sp_configure, does it still work as trace flag 3023?
Does it get reset when the SQL Server service is restared?
EXEC sp_configure 'backup checksum default', 1; GO; RECONFIGURE; GO
Thanks,
Santhosh
Human Knowledge Belongs To The World !!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

They do the same thing. The trace flag won't enable the option in sys.configurations (or sp_configure). It may be better to enable it with sp_configure as the trace flag was more for previous versions of SQL Server that didn't expose this option.I think it was added in SQL 2014. It's probably a bit more obvious when it's in the settings rather than trace flags.
You can check your backups to verify - the backupset table has the column has_backup_checksums

Sue

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 2

Avoid the trace flag. (as it is no longer a tweek) Use the configuration setting.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 3

Sue_H - Wednesday, April 5, 2017 8:02 AM
They do the same thing. The trace flag won't enable the option in sys.configurations (or sp_configure). It may be better to enable it with sp_configure as the trace flag was more for previous versions of SQL Server that didn't expose this option.I think it was added in SQL 2014. It's probably a bit more obvious when it's in the settings rather than trace flags.
You can check your backups to verify - the backupset table has the column has_backup_checksums
Sue

Yes, you are right. The option is added in SQL 2014 and it was not require to restart the SQL Service !!

Just noticed that they have removed "backup compression default" from sys.configurations in SQL 2014!!
Did Microsoft guys overwrite this with "backup checksum default" !! or is it just Express edition thing !SELECT value FROM sys.configurations WHERE name = 'backup compression default' ;

Version:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
    Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Sue_H SSC Guru Points: 90860 More actions · Answer 4

Santhoshkumar KB - Wednesday, April 5, 2017 8:14 AM
Yes, you are right. The option is added in SQL 2014 and it was not require to restart the SQL Service !!
Just noticed that they have removed "backup compression default" from sys.configurations in SQL 2014!!
Did Microsoft guys overwrite this with "backup checksum default" !! or is it just Express edition thing !SELECT value FROM sys.configurations WHERE name = 'backup compression default' ;
Version:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
    Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

It's an Express thing. Backup compression is not available with that edition.

Sue

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 5

Sue_H - Wednesday, April 5, 2017 8:28 AM
It's an Express thing. Backup compression is not available with that edition.
Sue

Got it thank you.

Thanks,
Santhosh

Human Knowledge Belongs To The World !!