In my case I've been reading Brad McGehee's guide to maintenance plans, and I've been looking at ways to put it into practice. Of course there is no single right way to create a maintenance plan. Just looking at backups, you can do it in any number of ways. Using a maintenance plan is one way, you could also use Red Gate Backup or another third party tool and set up a job for backup. Or I know a colleague who set up a cursor to work through each database in turn, backing it up. The important thing is that you take a backup, not how you do it.
But the virtue of maintenance plans, which came in with SQL Server 2005, is that they provide a clean straightforward way of performing routine maintenance tasks, without forcing you to do much in the way of hard work. That's the good thing; the bad thing is that you can't script them out and install them on another server - you have to create a new plan from scratch. Fortunately it's an easy drag and drop process. Maintenance plans use a subset of SSIS - SQL Server Integration Services - to carry out the necessary maintenance tasks. So you start by selecting a task from a "toolbox" menu, and dragging a box onto the screen. Each box describes a particular task: backup, update statistics, indexing, whatever - you configure it, and then go on to the next task. I find this an elegant way of doing stuff, particularly when you want to do things in order, and you don't want to start the second task until you are sure that the first thing has worked.
Here's one I did earlier - backing up over a network. This example shows another nice feature - see the red line? This tells the system what to do if there is a failure - if the first backup fails, take another backup to a different backup server.
Let me show you what works for me - and I'll try to talk about what you might like to change.
First of all, I like to set up two separate maintenance plans - one for the system databases (master, model, msdb) and another for all the user databases. The system DBs are usually relatively small, and straightforward, Having separate maintenance plans for System and User databases allow the System DBs to be backed up in seconds; a failure in the User databases does not stop the system databases from being backed up. If there is a failure, chances are it will be with one of the big user DBs, and by having a separate maintenance plan, I know that one important thing at least is working, and system DBs are fine.
The description is optional, but it is good practice to enter one, with initials and date. Each plan has appropriate sub-plans - for Daily, Weekly and Hourly Tasks as required. So for example:
- Daily Tasks
- Weekly Tasks
- Hourly tasks
- Daily Tasks
- Weekly Tasks
Notice I've given each sub-plan a schedule - it's probably better not to do this immediately, but wait until you have tested each element. At the very least, don't schedule the transaction log backups until you have taken a full backup!
Did I mention that SQL Server will create a log report of all your maintenance plan activities? A quick flashback to the menu - see the button that vaguely resembles two pieces of paper? If you hover over it, a tooltip pops up to say Reporting and Logging.
Clean out old log reportsRemember the reporting and logging folder which you added to your clipboard a few minutes ago? This is where you need it. The Maintenance Cleanup Task is usually used for clearing out old backups, but you can also use it here for clearing out old log reports. If you think about it, you have a couple of daily tasks and maybe a dozen hourly transaction log backup tasks. Over a few weeks, this will mount up. So the default is to sling those that are older than four weeks old. You don't have to - you can keep them for ever if you want, it's no skin off my nose - but four weeks seems reasonable.
Warning - SQL Server can be a bit flaky at deleting old files, especially things that aren't old BAK files. In theory, this was resolved years ago when they brought out Service Pack 2 for SQL Server 2005. In practice, it may work as designed, or it may not. If it doesn't, check out this blog post - Maintenance Plan Log Files Won't Die
Check Database IntegrityThis is going to check for corruption in your databases. It actually runs DBCC CheckDB(database) for each of the databases you select.
Don't believe me? Just press the View T-SQL button:
You can type out these commands yourself if you want, but why would you ever go to all that trouble? I think this is a good illustration of maintenance plans making life simple.
Here's the other good thing. In 99 times out of a hundred, probably even more often than that, DBCC CheckDB will find no problems. But that one time, there's a corruption in the database, and that's potential bad news. And if it happens, the maintenance plan stops, sends me a warning message (I'll get on to that) and doesn't back up the corrupt database or delete the old, good, backup. Turn it the other way round - by doing the check before taking the backup, you know that you are not backing up a corrupt database.
Update the statistics, and it could potentially make the queries run quicker. If statistics are out of date, the query execution plan is relying on out of date information and could make a bad decision. I've never seen proof of this in practice, but that might be because I like to update the statistics every day!
Back Up DatabaseThis is the big one, the one that really matters. If you don't back up your databases, perhaps DBA work is not the right niche for you? Here's how:
I'll stick with my policy of backing up all the user databases, and taking a full backup of all of them every night. But this might not always be possible. If a database is very large, it might be best to take a full backup at the weekend, and take differential backups on all other days. Check how long the backups are taking.
I like to create a sub-directory for every database - I find this particularly useful if I'm taking full and transaction log backups - this keeps them neatly together, and makes it easy to find the backups you need in a hurry. Not everyone likes this - try it both ways and decide.
I like to use the file extension usr.bak for user databases, sys.bak for system databases. This ensures that the maintenance cleanup task only clears out the correct backups. (It probably isn't critical, unless you have very different deletion schedules for user and system backups).
Here's the code to take the backup:
Then, if you tick the Verify box, it does an extra chunk of code, and runs restore with verify only - in other words, it simulates running a restore without actually doing so. This is a good test of whether you have a good backup or not. (The ultimate test, of course, is actually restoring your database - some would argue that this is the only test that matters).
The downside of running Verify is that the maintenance plan will take longer to run. Perhaps 10% longer, perhaps twice as long. The default is OFF, but I would always turn it on unless I have good reason to be concerned about running time. Try it and see.
Finally, if you are running SQL Server 2008 onwards, you can choose to compress your database. Third party products like the excellent Red Gate Backup offer different levels of compression; the native Microsoft product offers only on or off. On is fine - I don'treally see the value in fine-tuning the amount of compression - just do it! One word of warning - you can set the default for compression to be "Use default server setting", which sounds well and good until you realise that you don't know what the default server setting is. Best to explicitly say whether you want it or not, if you ask me.