IT professionals often make backups of their SQL databases using either native SQL maintenance plans or custom scripts. Others leverage third-party tools, which also work well. And when the backup indicates it’s “successful,” they believe all is well. But is it really?
Our aim here is to help you avoid avoiding a potentially false sense of security by relying solely on the “success” or “failure” of a backup job. One customer quickly discovered his database restore was failing due to corrupted pages. The customer had a couple of months’ worth of backups, but guess what? The corruption ran deep!
So, let’s take a step back at what happened and see why the backup job wasn’t reporting any failures, despite the corrupted files. When we run the BACKUP DATABASE command, typically we see something like this:
Here we’re simply backing up a DBATools database to a network share. When this backup is successful, the following message appears:
Looking at this, one would assume the backup was successful. But was it? Or would the outcome still display “success” if there was corruption in the backup set? The short answer is you betcha!
So how can we validate the success of our backup, really?
The key is to use the CHECKSUM option in your BACKUP DATABASE command, like so:
A quick word on CHECKSUM. This option will test page checksums on the data file pages being backed up. If a bad page checksum is found, the backup process will stop by default (we could override this behavior, but that’s a discussion for another day). So, if there is any corruption in the database (or bad checksums), a useable backup will not be generated until you fix the corruption.
In my next blog post, I’ll discuss Trace Flag 3023 (enables CHECKSUM on all database backups) which is a Ron Popeil, “set it and forget it,” solution. I’ll also provide additional recommendations for finding the true sense of security (and serenity) that comes with achieving solid, reliable backups for your enterprise.
If your organization would like help finding the ideal solution to ensure reliable backups every time, please don’t hesitate to reach out. We’d love to get you started.
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
|cookielawinfo-checbox-analytics||11 months||This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".|
|cookielawinfo-checbox-functional||11 months||The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".|
|cookielawinfo-checbox-others||11 months||This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.|
|cookielawinfo-checkbox-necessary||11 months||This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".|
|cookielawinfo-checkbox-performance||11 months||This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".|
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.