Most DBAs of any vendor’s system know how critical it is to back up your systems. User databases, system databases, etc. But do you ever restore them? That’s the only way to know if your backup is good. Of course, this represents a lot of difficulty if you’re managing a multi-terabyte system. I personally don’t have any experience with such, the largest that I’ve worked with was on the order of 150 gig.
Recently there was a post on SQL Server Central by a poor and most unfortunate soul who discovered that his system had found corruption on a production database. He had no backups, and apparently did not run DBCCs, and the error was one of those where your only options are to either restore from backup or to repair allowing dataloss. And without having a working backup, well…. Not a good position to be in, having to tell your bosses that when you get the database running again that you might lose information and have inconsistent data. Not good at all.
The sad part is that it’s not difficult to prevent. In SSMS expand the Management tree (I’m using 2014, but it’s been around for a long time), right click on Maintenance Plans, click on Maintenance Plan Wizard, and you’re on your way to getting automatic database consistency checks (DBCCs). AT THE LEAST you want to check Check Database Integrity, Update Statistics, Back Up Database (Full), and Back Up Database (Transaction Log). Clicking on Next lets you change the order that the tasks are run. Clicking on Next lets you select what databases this process will be run against. THIS IS IMPORTANT: you should probably check All Databases. System databases need love, too, particularly Master and MSDB. Master contains the metadata that defines your installation, MSDB contains your jobs and maintenance plans, along with information on backups, log shipping, sysmail, syspolicy, SSIS, etc. Model is important and should be checked, but it can be recreated from another installation, though you’ll lose customizations that you’ve done to it, if any. But it isn’t as likely to get corrupted since it is not normally transactionally active.
(you don’t have to worry about TempDB: it’s dropped and recreated every time SQL Server starts)
After you’ve selected the databases, Next will let you select where the report on all this activity goes, likewise you can have it mailed if you have the Database Mail services configured and running on the server.
And now you’re done. You’ve just taken some important steps towards protecting your data. But you’ve only taken first steps: you still have to review the reports and verify that the backups can be restored! If you don’t review the reports, how will you know your databases are clean and working properly? If you don’t restore your backups, how will you know if you can if you have a serious server crash or corruption issue?
Here’s the kicker: I don’t do maintenance plans, particularly for DBCCs. I’ll show you my personal method tomorrow.