Databases and newborns have a lot in common. They both require constant care. They both require constant monitoring. They also may both puke on you in the middle of the night. Unlike newborns, however, you can set up automated maintenance plans for databases. If you’re starting a new gig as a DBA, making sure your backups, index maintenance, and integrity checks are in order should be one of your first tasks. All of the jobs described below can be easily set up by creating a maintenance plan within SQL Server Agent or using a 3rd party tool.
Backups
Ah backups – the lifeblood of a DBA. Every database you support that isn’t extremely temporary (and probably those too) should be backed up on a consistent interval. Even the one that a developer tells you “Nah, this can be deleted next week.” – back it up. That same developer will probably be the one sending you an IM later that week saying “Hey super cool DBA, any chance you have a backup of that database I told you not to backup?” While all databases should be backed up, the level at which they are being backed up may differ, depending on the recovery model (Full, Simple, or Bulk Logged) and the recovery SLA (hopefully you have one) set forth by the business.
Full Backups
A full backup does just what it sounds like – backs up the database in its entirety. This file will serve as the start of the backup trail.
Differential Backups
Differential backups, while not necessarily required, can be a time saver and a major convenience if you need to recover a database. Lets say for example you took a full backup on Sunday, it’s now Friday, and you need to restore to Thursday. The full backup is only going to get you as far as when you took it on Sunday. If you were taking transaction log backups (discussed below), you could use those in conjunction with the full backup to get you to Thursday. The downfall with this is that you’re going to need every single transaction log backup from Sunday to Thursday to accomplish this. Depending on how frequently you’re backing up your transaction log, this may or may not be a tedious task. The other option? Take a differential backup each day you aren’t doing a full. This will let you recover to Thursday with two files – the full backup from Sunday, and the differential from Thursday.
Transaction Log Backups
If your database is using the simple recovery model (and you’ve verified this is appropriate), then transaction log backups won’t be possible. If you’re using the full recovery model, or even the lesser used bulk logged recovery model, you’ll most likely want to be backing up your transaction log. Taking a transaction log backup keeps the transaction log size in check, and also provides the ability for point in time recovery. Using the example described previously, lets say not only do you need to recover to Thursday, but exactly 2 PM on Thursday. You could use the full backup, plus all the transaction log backups taken up to 2 PM on Thursday. Alternatively, you could use the full backup, the latest differential, and all of the transaction logs since the latest differential, up to 2 PM on Thursday.
Index Maintenance
Throughout the course of the work week, your databases take a beating. Users are asking them to retrieve data as fast as possible, while also inserting and deleting data at a breakneck pace. At the beginning of each week, they need to be ready to be put through the meat grinder again. SQL Server is doing some of this TLC for you behinds the scenes with automatic update of statistics (on by default) and caching as much as it can to prevent going to disk to retrieve the data, but it still needs some help. Lend a hand by performing index maintenance when users are off the system. You could use the canned task that comes with SQL Agent, but this has a big problem. It is going to rebuild or reorganize all indexes for the database. If you’re dealing with a pretty small database then you can probably get away with this, but it’s still unnecessary I/O. With large databases, this just isn’t a good option. Instead, use a method that selectively rebuilds or reorganizes indexes based on the level of fragmentation. You could write your own, but I would recommended using Ola Hallengren’s solution. It’s free, constantly updated, and just works.
Integrity Checks
There is a lot going on behind the scenes of a database, and verifying the integrity is a must. The default maintenance plan task will work for most databases, but running integrity checks on a very large database can become challenging. This post from Paul Randal is a great resource on determining how to be successful running integrity checks on monster databases. Integrity checks are very I/O intensive, so along with scheduled index maintenance, try to schedule these during quiet time.
Data File Shrinks
Just kidding. Don’t ever make data file shrinks part of your regular database maintenance. Many have explained in detail why this is a bad idea so I won’t do it again here. If you’re wondering why, this list of links from Brent Ozar will take care of that for you.
If you’re struggling on deciding what backup schedule to use, start with weekly fulls, daily differentials, and hourly transaction logs and adjust as needed. Any backup schedule is better than no backup schedule. If you don’t have a SLA for recovery, talk to your manager and get one in place. Along with backups, remember to schedule index maintenance and integrity checks at an appropriate time – your databases will thank you.
Disclaimer: I am currently not providing primary care for a newborn.
The featured image on this post is courtesy of: https://www.flickr.com/photos/zzpza
