Time to read: ~ 3 minutes
Words: 531 words
Welcome to T-SQL Tuesday, the monthly blogging party where we are given a topic and have to talk about it. Today, we have Rob Farley ( blog | bluesky ), talking about integrity.
I’ll admit that it’s been a while since I’ve written a blog post. It’s been a combination of either burnout or busyness, but let’s see if I still have the old chops. Plus, I’m currently sick and resting in bed, so I have nothing better to do.
I’m one of the few who haven’t had experiences with corruption in our database. Apart from Steve Stedman’s ( blog ) Database Corruption Challenge, that is.
With that being said, what do I have to say about this topic then? Well, let’s talk about the first version of corruption checking automation that we introduced in work.
Now, this is just the bare bones and many different iterations since then, but the essence is here.
Overview
Like many shops out there, we can’t run corruption checking on our main production database instance. So, then, what do we do? We take the backups and restore them to a test instances, and then run corruption checking on those restored databases.
At least this way we can test the backups we take can be restored, as well.
But, I don’t want to spend every day manually restoring and corruption checking these databases, so let’s automate this bit…
Limitations
A quick scour of the interwebs brought back something extremely close to what I want by Madeira Data Solutions. It had some extras that I didn’t want, though.
More importantly, it used some functions that our dreaded antivirus software still screams false positives about. So, they would stop our script from running if we even tried.
So, a script re-write was required.
Script
Here’s the link to the script in GitHub if you want to check it out.
Use at your own peril. Also, default gist embedding on this platform is visible off-putting.
General Rundown
Here’s a general rundown of the parts of the code, just to make it easier to grok. It’s not to bump the word count on this post.
Cave Canum
This is an old version of the script. The first version, if my flu-riddled brain can remember correctly, so there are small bugs here.
Off the top of my head, I think it still:
- only works if the backup file is called FULL
- only works if there is a single FULL backup file per database ( _that got fixed real fast!_ )
- A.N.Other
Grab the Full Backup
Restore Everything on the Path
Faire le Corruption Check
Report on the results
Ar aon nós
Like I said, that script is the first iteration.
Use at your own peril.
Here be dragons and krakens and DBCC WRITEPAGE.
My main point is have the INTEGRITY to say “Yes, I have regular corruption checking of my databases”.
See what I did there?