I was pleasantly surprised to see a new “Backup checksum” checkbox in the Server Properties, Database Settings dialog page (as shown in Figure 1). This was something that I had been asking for for quite awhile, so I appreciate that it has been added to SSMS.

This simply provides tool support for the backup checksum default sp_configure option that was added in SQL Server 2014. This just adds the CHECKSUM keyword to your backup commands by default (in case you forgot to add it or some non-native backup solution is being used). You can override this default by specifying WITH NO_CHECKSUM in a backup command.

This gives you a little bit of extra protection for your database backups, since the backup operation verifies each page for checksum and torn page, and generates a checksum for the entire backup. This is not a substitute for actually restoring you database backups on a regular basis, but it is a useful extra step to help ensure that your backups are actually good.

Just to be clear, this is not new functionality, it just adds tool support in SSMS, so you don’t have to use T-SQL to enable this setting. This makes it more likely that more people will start using backup checksums, which is a good thing.

Microsoft has some more information about backup checksums:

Enable or Disable Backup Checksums During Backup or Restore (SQL Server)

How to enable the CHECKSUM option if backup utilities do not expose the option

Older versions of SQL Server can use trace flag 3023 to get the same effect.

image

Figure 1: SSMS 18.0 Server Properties, Database Settings Dialog