Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Exporting and Importing SQL Server 2005 Database Maintenance Plans

Created: 14 Jul 2009 | 3 comments
Language Translations
ianatkin's picture
+6 6 Votes
Login to vote

Once Altiris has been setup, configured and is up and running, one of the underpinning infrastructure elements often falls to the wayside and gets forgotten about. What I'm talking about here is SQL Server. And its completely understandable - most Altiris Administrators have difficulty enough getting official Notification Server training which regrettably leaves SQL Server training way down on the wish-list. This is fine until things go horribly wrong and you need to grab some backups, and they aren't there. Putting in the maintenance plan was always something you were going to do....

I recently performed an audit of my SQL Server 2005 database infrastructure. When I delved into the maintenance plans I was fairly surprised to find that one was still using its legacy plan (from its previous incarnation as SQL Server 2000), and a couple had no maintenance plans at all! As a result, I decided it was time to centrally manage and distribute these plans - our Deployment Server count was rising and this picture could only get worse.

If you operate many Deployment Servers in your organisation (or even simply have many SQL servers to maintain) then trust me when I say this article can help you save a lot of time, and perhaps even a few blushes when you find how badly in order your own house is.

Overview

SQL Server 2005 has some great in-built functionality when it comes to database maintenance plans. In particular, it has some nice workflow logic which is editable through an intuitive interface. This means you don't have to write any T-SQL to create these plans -something which was hard to avoid if you wanted good DB maintenance plans on SQL 2000. The nice maintenance plan designer though is severely let down by issues which emerge when exporting and importing these plans -a necessary step should you want to replicate such plans across your SQL environment.

This article shows you the steps you need to take if you want to rationalise the maintenance plans across your SQL estate by showing you how to create, export and import plans on your SQL 2005 servers. Whilst these steps are probably not worth your while for simple plans, they will save you a ton of time should you start implementing complex plans, or require less-skilled staff to setup and configure your SQL Servers.

At the end of the article, I also provide some troubleshooting tips which include a very handy database maintenance plan clean-up script. If you start creating and deleting plans you too will find this useful as it helps delete stuck plans in the SQL system tables.

Creating a Database Maintenance Plan

The first step in this process is to create a database maintenance plan. Let me take you through the steps I went though on my Deployment Server ALTIRIS-LIB.

  1. Open-up Microsoft SQL Server Management Studio, and connect to your SQL Server. Under the "Management" folder browse to the folder "Maintenance Plans"

    imagebrowser image

  2. Right-click the "Maintenance Plans" folder and select "New Maintenance Plan..." from the context menu.
  3. Now to choose a name for the maintenance plan. The parser has a few restrictions on characters here, most are acceptable. Notable illegal characters are the colon (:), the semi-colon (;) and the full-stop (.).

    As I intend my plan to backup the express database on a nightly schedule, I've named it as shown below,

    imagebrowser image

  4. Once we've entered our plan name, we'll get straight into the design tab for the database maintenance workflow. The way this works is *really* simple -just drag items from the toolbox on the lower-left pane and then drop it into the designer window and connect the bottom of one item to the top of another to move the execution to the next element once it completes.

    Do this now for the "Check Database Integrity Task" toolbox item. Select if from the toolbox pane, and drag it into the designer window. You should now have a screen similar to that below,

    imagebrowser image

  5. Now right-click the "Check Database Integrity Task" object and select "Edit" (or double-click as Edit is the default execution option

    imagebrowser image

  6. This will bring up a dialog which allows you to select the databases for the integrity check. To ensure the integrity check is performed on the eXpress database, click the drop-down selection box, and check the 'eXpress' database from the list and click OK.

    imagebrowser image

  7. Now to the cool bit. Notice the green arrow below the Integrity Task toolbox object? This is to allow us to connect the item to other tasks, depending whether this task succeeded, failed or simply completed (i.e. you don't care whether it passed or failed)

    Let's now make the workflow execute the "Rebuild Index task" when the database integrity task finishes. To do this, drag the "Rebuild Index task" item into the workflow pane. Configure it by double-clicking and selecting once again to perform this task on the eXpress database.

    Note: the default option for reorganising the pages with the default amount of free space is absolutely fine. This rebuilds the indexes with the fill-factor specified when the indexes were created, which normally equates to 10% free space.

  8. Now select the "Check Database Integrity Task" object so that the green link arrow appears. Click the arrow (to make it mobile), and then select the rebuild index task. This links the tasks so that when the integrity task completes, the indexes are rebuilt.

    imagebrowser image

  9. Now let's add the actual database backup to the workflow. Drag the "Backup Database Task" toolbox item into the workflow pane, and connect as above to the "Rebuild Index Task" item.

    Double-Click the "Back Up Database Task" item, and configure the specific database eXpress as the backup target. For neatness, I also select the option to create a sub-directory for each database, and get the backup task to verify the integrity too as shown below,

    imagebrowser image

    Your workflow should now look as below,

    imagebrowser image

  10. The next bit is really important -you must save the plan. On the SQL Server Management Studio toolbar, find that legacy (but oddly comforting) floppy disk icon and click it. If you do not, all your work will be lost.
  11. Once you've saved the plan, you should see it in the maintenance plan folder,

    imagebrowser image

    Right-Click the plan, and execute it. With luck, all should proceed smoothly as illustrated in the dialog below,

    imagebrowser image

And that's it. You should now have a basic plan up and running -about two minutes work.

Once you get more used using the designer window, you can now add tasks to prune out of date backups (Maintenance Cleanup Task), or even to prune the log files (History Cleanup Task). For those who really get the knack of it, best practice also advises to add a notification at the end to alert you when the maintenance task completes, and to add extra failure logic to let you know when and where it went wrong.

But for the illustration of this simple import/export process this little plan will suffice, so let's move on.

Exporting a maintenance plan

It's odd (and frustrating) that we can't just right-click a maintenance plan and export it in SQL Server Management Studio. Perhaps MS will add that functionality directly one day, but for now we must resort to SQL Server's Integration Services. This has the ability to perform data transformations which will allow us to extract a maintenance plan from the database and convert it into a Data Transformation Services (DTS) package. As DTS packages can be imported on other SQL Servers, this provides the basis for our Database Maintenance Plan replication.

So, in order to export (and indeed import) maintenance plans, you need to have SQL Server Integration Services installed.

Accessing Integration Services

To see if you've got the Integration Services installed, from Microsoft SQL Server Management Studio click the Connect button in Object explorer. You should see a services drop-down as shown below,

imagebrowser image

As you can see from the above screen grab, this reveals an assortment of SQL Services you can connect to. Here is the list with a brief description of each,

  1. Database Engine
    --The core SQL Server engine
  2. Analysis Services
    -Adds data mining capabilities to SQL Server
  3. Integration Services
    -- Provides functionality to extract, query and transform data from various sources for loading onto other sources.
  4. Reporting Services
    --Report generating environment for SQL Server databases
  5. SQL Server Compact Edition
    -- The free embedded database engine targeted at developers of Windows Mobile and Smartphone applications

Now select the Integration Services component. You should see the authentication dialog popup at this point,

imagebrowser image

Click Connect, and your object explorer should populate with the Integration Services component as shown below,

imagebrowser image

If you do not have Integration Services installed, you'll need to re-run your SQL Server 2005 Setup and add this component. Note: You will have to re-apply any service packs afterward. At the time of writing, the current SQL Server service pack is SP3, and I highly advise applying this pack across your SQL Server estate as this contains some important bug fixes relevant to the maintenance plans.

Creating your Data Transform File

Now, to export your maintenance plan do the following,

  1. Under Integration Services, locate the 'Nightly Express Backup' maintenance plan under Stored Packages --> MSDB --> Maintenance Plan
  2. Right-click the plan and select "Export Package"
  3. In the Export Package Window, you'll need to change the default option to export your package to another SQL Server. Choose in "Package Location" the target to be the file system, and then select the desktop as the path as depicted below and click "OK"

    imagebrowser image

And that's it -pretty simple. You should now see on your desktop the dtsx file which you now store away safely with your central server setup configuration files. In practice, it's also probably a good idea to give this file a version number which can be referenced in your internal docs which describe the plan.

Importing a maintenance plan

The strangest thing with the export/import process of maintenance plans is that it's really dumb. Amazingly dumb really. What the export/import process does when copying plans from one server to another is to copy the plan exactly -right down to the server it's attempting to connect to! This means that any plan you import will still think its trying to perform a backup on the server you exported it from. So, before importing you centralised plan, you need to copy it to you target server and make a small edit to change the connection string.

  1. Check you have Integration Services installed. If not, rectify as per pervious instructions.
  2. Copy your centralised plan (which in my case was made on ALTIRIS-LIB) to your target server. Put it somewhere easy to access (like the desktop).
  3. Open the plan up in notepad, and search for the string ConnectionString,

    You should find the following line of text,

      
    DTS:Name="ConnectionString">server='ALTIRIS-LIB';Trusted_Connection=true;Application Name='Microsoft SQL Server Management Studio';Pooling=false;Packet
    

    Now change the ConnectionString to reflect the netbios name of your target server (in my case my target server is called URS-DS)

    DTS:Name="ConnectionString">server='URS-DS';Trusted_Connection=true;Application Name='Microsoft SQL Server Management Studio';Pooling=false;Packet
    

    The above line is now in a form which can be imported nicely to the server URS-DS

  4. Save the modified dtsx file.
  5. Open up SQL Server Management Studio and connect to both the local database and Integration Services
  6. Under Integration Services, locate the maintenance plan folder, under Stored Packages --> MSDB --> Maintenance Plans
  7. Right-click the plan and select "Import Package"
  8. In the Import Package Window, you'll need to change the default package location option (it again assumes the package is hidden with another SQL Server database, rather than being present as a file). Choose in "Package Location" the target to be the file system, and then select the path to you modified dtsx package
  9. Next, click in the package name field to pre-populate it with the default package name and click OK. In the example below, I'm importing the modified dtsx file located on the desktop. Change this as required to match wherever you dropped your modified export package.

You should now see you imported maintenance plan as I have shown below for the SQL Server URS-DS,

imagebrowser image

Setting Up the Schedule

It is important now to configure the run schedule for the plan. Don't skip this section -this section creates the SQL Server Agent job which critical to executing the plan.

To configure a schedule do the following,

  1. In SQL Server Management Studio navigate to the maintenance plan.
  2. Double-click the plan to open it in the designer window
  3. Click the calendar icon to open up the "Job Schedule Properties" window
  4. Configure the plan to run as per your requirements. In the example below, I've selected the plan to run daily at 11:30pm

    imagebrowser image

  5. Click OK

And that's it. You should now be able to execute your maintenance plan.

Troubleshooting

SQL Server maintenance plans with Microsoft are really a bit of a work in progress. This is a great shame, and the developer team are aware of the shortcomings. There are a couple of issues which can manifest when playing with maintenance plans. Here are their workarounds,

The 'job delete' bug

In some circumstances, when you delete maintenance plans, you can find yourself unable to delete the associated job. This is because the primary & foreign key relationships linking the four system tables used for maintenance plans are not adequately scavenged when you delete a plans -there are no triggers which then cascade these deletions through the tables. As a result, you lose referential integrity and you can get errors like,

imagebrowser image

To fix this, the following SQL script was developed by Gedas Gudenas to clean up the references to the old plans across these tables, thereby allowing you to complete your job deletions through the GUI,

USE [msdb]
declare @job_name varchar(100)
set @job_name = N'DELNightly Express Backup.Subplan_1'


/* First, delete the logs for the plan */

delete sysmaintplan_log
FROM sysmaintplan_subplans AS subplans INNER JOIN
sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id INNER JOIN
sysmaintplan_log ON subplans.subplan_id = sysmaintplan_log.subplan_id
WHERE (syjobs.name = @job_name)

/* delete the subplan */

delete sysmaintplan_subplans
FROM sysmaintplan_subplans AS subplans INNER JOIN
sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id
WHERE (syjobs.name = @job_name)

'Check Database Integrity Task' crashing Maintenance Plans

On some of your SQL Servers, you might find your maintenance plans (whether imported or not) fail consistently with the following generic UI error,

"Execution failed. See the maintenance Plan and SQL Server Agent Job History Logs for details.
Additional Information: Job ;Maintenance Plan.subplan_1 failed.
(SqlManagerUI) "

This is a generic execution failure message. Normally you would take a look in the SQL Agent and SQL Server logs, but in this case nothing at all is recorded for the period over which the plan was executed.

If the maintenance plan contains the task 'Check Database Integrity', try removing this task and save the plan. If you can now re-run the task successfully, its possible you've had the global setting allow_update toggled from its default value of zero.

To resolve, run the following T-SQL in query analyser

reconfigure with override
sp_configure 'allow_update', 0
reconfigure with override

Maintenance Plan fails due to NT Groups Error

If you receive the generic UI error and you find the SQL Server Agent logs have the entry like,

SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'mydomain\myusername', error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

This most likely means your plan is trying to execute with the context of domain user, and domain authorisation is hindered as the SQL Server is running under a local service account.

To resolve, the best thing to do is to run SQL Server under a domain account. If this is not possible, you'll have to keep editing the maintenance plan job under SQL Server Agent -> Jobs, changing the job owner from the domain account listed to the local administrator.

It's is possible you can avoid this with imported plans by editing the dtsx file to change the creator/owner from the domain account to the local admin account on the SQL Server. Doing this might prevent the constant ownership shift which appears to happen when you access plans using your domain account.

Summary

Today I have tried to encapsulate what I have learned over the last (and very painful) week working with SQL 2005 Server database maintenance plans. As you can see, the new plan format with SQL 2005 Server has it's idiosyncrasies, but after you've leaned the niggles they are a very powerful tool. If you plan to develop plans with error-flow, I highly advice using the techniques in this article to backup and restore plans on your servers. The main reason being that once you've created such plans they are difficult and time consuming to test, and this is something you'll have to do if creating them from scratch each time. This article allows you to create a gold master if you like -a full tested and backed up plan which you can replicate with confidence across your SQL estate.

For those who have attempted using plans, but just hit too many problems in the process I hope my troubleshooting tips above help.

May your backups never fail!

Kind Regards,
Ian./

Comments 3 CommentsJump to latest comment

ianatkin's picture

Hi Fireeyes,

Could you let me know which step 9, and where you are finding difficulty?

Kind Regards
Ian./ 


Ian Atkin, IT Services, Oxford University, UK

Connect Etiquette: "Mark as Solution" those posts which assist you most in resolving your problem, and give a thumbs up to useful articles and downloads

0
Login to vote
KSchroeder's picture

Outstanding work as always Ian, reading your articles is always a pleasure, and informative to boot!  At my company we are fortunate enough to have a SQL DBA team to setup these plans, but it never hurts to PYAITK anyway, in case of a rainy day!

Thanks,
Kyle
Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

+1
Login to vote