2

I am in the process of adding the WITH CHECKSUM flag on our daily SQL backups, as part of an effort to better ensure data integrity.

I definitely want to know if a checksum error is ever encountered, but I also don't want my job to stop dead in the water in the middle of the night; I want it to finish backing up the "bad" database, then continue backing up the other databases on the server.

If I use BACKUP WITH CHECKSUM, CONTINUE_AFTER_ERROR, will it still throw the appropriate error (SEV 22 or Error 825 or whatever) that will trigger my associated alert? Or does CONTINUE_AFTER_ERROR suppress this altogether, and I'd only know about the problem if I parse the job step output?

I would simply test it, but I don't have a database with known CHECKSUM inconsistencies.

2

Yes it will continue and finish, there is an example in this older blogpost. It also contains a broken database file (for older versions) to play with:

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2007/04/17/example-corrupt-database-to-play-with-and-some-backuprestore-things-to-try/

I think it is actually quite important as a dba to simulate all those cases, I know it from experience with customers it is risky and time consuming if you have to research those methods in a downtime. So kudos for asking. Here is a lunatic/ingenious method to do corruption with SQL:

https://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/03/10/how-to-create-a-corrupt-database-using-bulk-insert-update-and-bcp-sql-server-as-a-hex-editor.aspx

  • 1
    Thanks, @eckes, this led me in the right direction. I did get some interesting results I will post in my own answer. – BradC May 13 '19 at 20:31
1

With a minor change to Brent Ozar's instructions (I used SET PAGE_VERIFY CHECKSUM instead of SET PAGE_VERIFY NONE), I was able to create a small database with a corrupted page (and therefore a mismatched checksum) in a lab environment, to fully test all the possible backups options.

This gave me the answer to my question: Yes, if you do a BACKUP WITH CHECKSUM, CONTINUE_AFTER_ERROR, it will still throw an error, but that error will not be Error: 824, Severity: 24, it will be Error: 3043, Severity: 16.

I don't currently alert on all Sev 16 errors (would rather not get a page every time someone makes a typo in a query), so I had to add a new alert for Error 3043 to get notified when this issue occurs.

My final takeaway, then, is: If you enable backup checksum, makes sure you are also alerting on Error 3043.

Some additional reading and resources:

  • Thanks Brad for the complete writeup – eckes May 13 '19 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.