Will I be notified that BACKUP encountered a CHECKSUM error if I use CONTINUE_AFTER_ERROR?

Question

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.

Answer 1

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

Answer 2

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:

• Possible Media Errors During Backup and Restore (SQL Server) MS Reference detail about backup checksums
• Example corrupt database to play with and some backup/restore things to try: Originally linked by @eckes, this Paul Randal article has excellent content, but the example is SQL 2005.
• Brent Ozar: Let’s Corrupt a SQL Server Database Together, Part 1: Clustered Indexes. Clear examples, thanks Brent for posting this. And if you liked that one, don't miss Part 2: Nonclustered Indexes and Part 3: Detecting Corruption!
• Steve Stedman's Database Corruption Challenge: Increasingly difficult challenges in diagnosing and fixing database corruption. Solutions to each are posted.
• DBA SE: What types of corruption can DBCC CheckDB miss? The most relevant related question I could find on here.