by Jason Hall
Continuing a string of posts on tips and tricks for LiteSpeed, I thought I would take an opportunity to discuss how LiteSpeed allows you to select databases to backup in a maintenance plan. Whether or not to use maintenance plans to backup your SQL Servers is very much a matter of personal preference. LiteSpeed has added some great functionality to make maintenance plans more configurable than what you get with SQL Server.
Consider the screen that allows you to select which databases to backup. In SSMS 2008 (and 2005/2000) you can select to backup either all databases, all system DB's, all user DB's, or you can select a subset of your DB's to backup with an associated plan.
Native Database Selector
There are two main challenges with manually selecting a subset of your databases.
- Once you select a subset of your databases, any new database added to the system will not be automatically picked up by the maintenance plan.
- The order in which the databases are backed up is not configurable. The critically of your databases is not determined alphabetically, and if you have a maintenance plan run long, wouldn't you want your most important databases backed up first?
LiteSpeed Maintenance Plans have addressed these two concerns. In the database selector for a LiteSpeed maintenance plan, you have two options that you don't get with native plans.
- LiteSpeed lets you select databases for exclusion (not inclusion as in a native plan). Essentially, you are telling LiteSpeed to backup all databases except the chosen databases. This means that any newly created databases will be picked up by your plan, yet you can define databases that should never be backed up.
- You can order the databases that you select. By moving databases up and down you can determine which databases get backed up first. I highly recommend that you let your maintenance plan tackle the most critical databases first so that should anything occur during your maintenance window, your most critical databases have a higher probability of completing successfully.
In the example above, master and msdb are backed up first, followed by the Quest repositories, followed by the remainder of my databases.
As you can see, not only does LiteSpeed compress and/or encrypt your database backups, but also has functionality built in to assist with the management of your backups as well. Plenty more tips and tricks to come. If you have any questions about this or anything else, feel free to comment or visit the forums.
