Today we’ll do the 4th and final part of the Maintenance Plan Wizard, which covers Backups and keeping your hard disks clean!
Back Up Database (Full)
The jobs get more obvious as they go on, making my explanations a bit easier! This takes a full copy of your database and dumps it to a file (usually .bak). If possible, run this every night for all of your databases, as these are the jobs that will save your neck should you lose your database/server. Best practice is to back up to a network share to make sure your backups are completely redundant, but with limitations on network speeds vs database sizes, a local backup which is copied off to a share once it is completed is also fine, but just be aware of the risks. If your database is small enough (and by small I mean under 1TB) and your server has the space, you should be able to do full backups every night.
Back Up Database (Differential)
This task performs a differential backup (duh!). What this means is it only backs up the changes that have occurred since your last full backup. So with larger databases, maybe you only have time/resource to take one full backup a week. Differential backups would be the next order of business then to make sure you have up to date data. So if you take a full backup on a Sunday, and a differential backup every other day, and your database fails on Thursday, you would restore the full backup from Sunday, and the differential from Wednesday/Thursday (depending on if Thursdays backup succeeded), and you’d be back in business. Basically they are cumulative, and don’t have to be restored in order like a transaction log.
Back up Database (Transaction Log)
This backup seems similar to a differential backup, but it is more fine grain, and also backs up your transaction log, not your database. So not the same at all! If your database is in “Full” or “Bulk Logged” recovery mode (Set in your database Properties -> Options), you want to be backing up your transaction logs. The frequency is entirely up to you, depending on what is an acceptable amount of data you can handle losing in the event of a system failure. If it’s an hour, then set your Log backups to run on the hour. 15 minutes, run them every 15 minutes etc.
The restore process for transaction logs is not cumulative, so that means you have to restore each transaction log in order, up to the most recent one. Restore strategies pulls me a bit off topic, but have a read of this Technet article for a high level overview of strategies.
The other reason to have regular transaction log backups is to keep your .LDF file size down. Everytime you take a log backup, it clears space in your .LDF by telling SQL “Ok, this data is now committed to disk, I don’t need to hang on to it anymore”, and it clears out the un-needed space. (If you feel really brave, this is how Transaction Logs work). If you don’t clear this out regularly, it will just keep appending data on to your file, and just keep growing, regardless of the size of the data in your database, as it records every change that occurs in your database.
Maintenance Cleanup Task
This task goes off and scans a folder, based on some conditions you set, and cleans up any files that meet those conditions. So for a real world example, you tell it to scan your Backups folder for any backups over 3 days old, and clear them out because you’ll never want to go back that far. You can tell it to do the same from transaction log backups too, and then you never have to worry about backups filling up your disk on you.
So that’s it for the series, I hope this has given you a useful overview of the wizard and what it does. If you do feel brave and want to get your hands really dirty with it, Brad McGehee has an excellent book on Maintenance Plans. It’s available to purchase on hard copy, but also has a FREE ebook version. Have a look at it here and browse at your leisure.
As always, any questions, please don’t hesitate to ask.
Stephen