Introduction

In SQL Server 2000, many administrators came to rely upon maintenance plans to perform the day-to-day upkeep on their databases. With the simple wizard interface and relatively good default options for the various tasks, setting up basic database maintenance tasks on a new server was accomplished quickly. It wasn't perfect, but it worked. With the release of SQL Server 2005, the response from the DBA community to the changes in maintenance plans has been mixed. The maintenance plan functionality has been merged into SSIS (Microsoft's renamed DTS), which this author believes is a good move as a whole. You get the ability to bundle tasks together more easily allowing flow control to execute the next step in a maintenance plan. In 2000 we would've had to either guess the schedules so as to try and keep performance-intensive jobs from overlapping, or create many plans each with their own jobs, and have each job queue the next.

However, to complicate matters new problems have been introduced. The largest complaint I've seen about the new maintenance plans is how they handle backups. In SQL Server 2000, it was not uncommon to set up a maintenance plan where all user databases had full backups taken nightly and stored for a few days on disk before being removed automatically by the server. Log backups would be taken every few hours and stored for 24 hours and then removed. Usually a tape backup process would pick up the files nightly and store them. You have the same options in 2005, however if you elect to store your backups as individual files and sort them into separate folders, they will no longer be automatically removed after a pre-set retention period. Several of the new problems were well documented by Andrew Calvett in his blog, those of you who are curious would do well to read that entry (and his entire blog, for that matter).

The goal of this article is to provide a workaround for this situation and crack the lid of some of the new features and tools in SQL Server 2005. Microsoft has made some noise that they will be addressing the issues with maintenance plans, however we're not sure when that will come about. For this article, I would highly recommend you download the code, open it in SQL Server Management Studio, and follow along. It's written as a SQL Server template and the appropriate values can be filled in by going to Query | Specify Values for Template Parameters... I would also make note that the code you download is somewhat slimmed down from what I'm actually using. I like to customize and get notifications and generally not have to babysit my servers too much... but I'll leave the improvements and extensibility up to you. Suffice to say because it's all done in script you can pretty much customize to your heart's content. Right now we'll just be scratching the surface and hopefully breaking away from the limitations imposed by the GUI.

Sneaky Tricks With Extended Stored Procedures

For this example, my backup plan on this server is to have all system databases backed up nightly and stored on disk for 3 days. User databases should have full backups taken every night and stored on disk for 5 days. Log backups should be taken of user databases every four hours and stored on disk for 24 hours.

The first step is to create a workspace database on your server. In my opinion every DBA should have a place where they store miscellaneous data, scripts, or what have you. If you already have one, comment out the CREATE DATABASE statement. We're going to create a schema and a few tables to track our backups (you do want some sort of logging don't you?) as well as a table for databases we want to ignore. For my own sake, I don't care about the AdventureWorks databases, so I'm going to include those here.

Next we call out to the extended stored procedure master.dbo.xp_create_subdir to create the directories to store our databases. Then we create the job to backup our system databases. If you do not have a lot of experience with dynamic SQL, this might look a little daunting to you. One thing many people aren't aware of is how SQL Server handles single quotes in strings. When SQL evaluates a string like:

'INSERT table VALUES (''Aaron'', ''Ingold'', ''Database Admin'')'

It treats each double single-quote ('') as a single-quote. Thus that string becomes:

INSERT table VALUES ('Aaron', 'Ingold', 'Database Admin')

If you'd like to see what the string actually does, it's simple enough to copy and paste it into a new query window, remove the leading and trailing single-quotes and then do a find and replace on all double single-quotes ('') to just single-quotes ('). You'll note that we're giving the backup a dynamically created file name within the job step. This allows us to embed into the file name the date when it was initiated. If we did not change the file name with each backup, SQL would happily append the backup to the existing media each time. Thus if you discovered a problem on Monday with a critical database and wanted to restore from Friday's backup, you might have to recover the file from tape first. This could add a great deal of time to your recovery (and invalidates the whole reason behind having multiple days of backups on disk in the first place). We also store an entry into db_full_backups of the database, file location, when we took each backup and when it expires.

We repeat the steps above for each of our system databases (there's no need to backup tempdb), and then it gets a little more tricky. Thus far we have all the critical information we need to identify which backup files to remove:

1. A record of the files we created.
2. When they should expire.
3. Whether or not we've already taken care of them.

In the final step of the job, we declare a cursor for the system database backup files that have been expired but not removed. A lot of discussion has been had on various blogs and message boards about the use of the undocumented extended stored procedure in the master database sys.xp_delete_file. After some trial and error I believe I've figured out this tool. It seems to take two arguments, the first is a bit datatype with 0 indicating you want to delete a backup file and 1 indicating you want to delete a maintenance plan log file. The second is an nvarchar datatype of the full path to the file on the server you want to delete (I have not experimented with UNC paths yet). Inside the cursor we execute this procedure to delete our expired backup file, update our backup log appropriately and get the next file.

User databases are handled in much the same way, however since we cannot count on which databases will be on the server (and we want to automatically back up new databases) we have to change a few things. Firstly we call out to another undocumented extended stored procedure, master.sys.xp_dirtree to read the subdirectories in our backup directory into a table datatype. This list of backup directories can later be compared against master.sys.databases (our database list on the server) to determine which subdirectories we need to create. We also declare a similar cursor for the databases to back up, excluding system databases and anything we've elected to ignore via ignore_databases. Then we follow a similar process to what we did with our system databases, though we check and create the backup directory if needed using xp_create_subdir. This means that any newly created databases will automatically be backed up with this job and the appropriate subdirectories will be created.

Moving on we do the same thing with the log backup. The job should be familiar by now, except we log everything to a different table, just to keep things organized. There are also a few more exclusions for what gets backed up. Firstly, log backups on databases with the simple recovery model will fail. Also log backups in SQL 2005 when there is no full database backup will fail (hence the reason we check the create_date value to be sure a day has passed and assumedly a full database backup has been taken, if you're not going to do daily backups you should modify this accordingly).

Still with me? Good!

Wrapping Up and Suggestions for Better Functionality...

Now the astute reader will notice that I did not schedule the jobs. This is because job scheduling is one task I always prefer to do via the GUI. I took my regular maintenance plan for system databases, added an Execute SQL Server Agent Job Task as the final step and added the appropriate full backup job to it. This way my backups kick off immediately after my other maintenance plan tasks. For the log backup, I opened the job in Management Studio and scheduled it to run every 4 hours.

As I said earlier, I would recommend you don't stop here. This has some potential flaws and is not a perfect solution. Because all the backup jobs for user databases are enclosed in a single step, it's possible that if a single backup fails the entire job will quit out reporting failure and other database backups won't even be attempted. I configured myself as an operator and configured notification options on each of the jobs on failure. Using database mail and DDL triggers I configured triggers to fire for CREATE and DROP DATABASE statements so I could update my maintenance plans accordingly. It's also important to take security considerations into account as the context under which the jobs are executed must have execute rights to the extended stored procedures we use as well as read/write access into the backup directory. Finally, it is easily possible to see how this can be expanded beyond just backups and restores. Entire maintenance plans can, (and in my opinion should,) be scripted in this fashion. It's more robust, allows for you to replace SQL Server's less than helpful logging with your own and gives you more fine-grained control over your server operations.

Conclusion

With any luck, Microsoft will be releasing an update to SQL Server Management Studio which makes maintenance plans a little more viable of an option. In my experience too few DBA's leverage the power of SQL as a scripting language for database maintenance. With a little knowledge of SQL Server's system tables, how to use dynamic SQL and the willingness to figure out some of the less documented tools that Microsoft has built into SQL Server a lot of time can be saved on the more mundane tasks.

The information expressed in this article represents the author's own views and is not endorsed by the author's employer.