For any SQL Server DBA who’s been in the field for a long time, you know that the exercise of restoring backups taken in SQL Server 2000 (and even SQL Server 2005 without using the CHECKSUM option) was many times a sweat-filled lottery. You usually gambled and/ or prayed to your respective deity that your backups would actually work. Why? Because you knew that restore failures were all too common. Despite SQL Server’s assurance that all was well because you had already verified your backups with the RESTORE VERIFYONLY option. So why was this the case? Why did restore operations fail so frequently? Let’s delve a bit more into the innards of the RESTORE VERIFYONLY and CHECKSUM options to find out.

RESTORE VERIFYONLY & CHECKSUM

First some background information. The RESTORE VERIFYONLY option is the next best thing to actually restore your backup file, in order to confirm that it is actually usable. When you execute

RESTORE VERIFYONLY

FROM <BACKUP DEVICE> = ‘filename’

SQL Server confirms that the file follows the correct specification and is actually readable, but does not actually restore it.

During SQL Server’s Jurassic period, a.k.a SQL Server 2000, the RESTORE VERIFYONLY option did already exist. But unfortunately, all it did was to confirm that your backup file conforms to the Microsoft Tape Format (MTF) specification and that the file could be read from disk. The file could very well be corrupted and SQL Server would still ‘certify’ it as A-OK. This behavior persisted up to SQL Server 2005 but was remedied in 2008 and subsequent versions.

Hence the need for the BACKUP…WITH CHECKSUM option while taking backups, especially for the earlier SQL Server versions. This introduces a checksum into each page of the backup file. So that even if a single byte is changed, say due to file corruption, the RESTORE…VERIFYONLY option will fail and you will at least know not to rely on that backup file. Read more about how to enable or disable backup checksum on the official Microsoft page here. In some rare cases, your backup utility may not have access to the  CHECKSUM option (such as when using SQL Server maintenance plans). In that case, you can enable the option using the trace flag 3023.

So What’s the Downside?

In case it still isn’t clear, it’s a good idea to ALWAYS take your backups using the CHECKSUM option and confirm your backups are ok using the RESTORE VERIFYONLY option. These two options complement each other and should be used together – yin and yang.

Now you may be thinking: “Hey, this CHECKSUM feature sounds wonderful. Why on earth is it even optional, why not make it standard?” The answer, dear asker of pertinent questions, is because it somewhat costly in terms of hardware resources. Including the CHECKSUM option will make your backups take a little longer because of the higher CPU overhead, plus your restoration will also take a bit longer. This is especially noticeable for very large databases, in the order of tens or hundreds of GB. Still, it is highly recommended to always use the CHECKSUM option for your backups and periodically verify the backup files with the RESTORE VERIFYONLY command.

A Good, Permanent Solution

That said, you have 3 options for making use of the CHECKSUM and VERIFYONLY options in your environment. You can trust yourself to always remember to include the CHECKSUM and RESTORE VERIFYONLY options. Or you can create an automated script to achieve the same thing if you have the skill to do this. Or you can employ an easy-to-use tool like SQLBackupAndFTP to accomplish this for you. With SQLBackupAndFTP you simply create your backup jobs and define the locations where they should be saved (and these can be local drives, remote FTP locations, or in the cloud). Then activate and select the options to always add a CHECKSUM to your backups and run a RESTORE VERIFYONLY after each backup. These options are set in the “Backup options” section.

Conclusion

Now you know about (and should start using!) the CHECKSUM option in your SQL Server backups, and how to take advantage of the RESTORE VERIFYONLY command to ensure that your database backups won’t let you down when you need them the most. And also that SQLBackupAndFTP is an excellent answer to your need for a simple, useful set-and-forget tool to enable you to always take advantage of these features in your backup regimen.