Automating Deployment of SQL Server Maintenance Plans

Introduction

SQL Server maintenance plans provide automated backups of SQL Server databases. What if you want the same maintenance plan to be deployed to one or more additional servers? The deployment can be automated as explained here using SQL Server 2012 for the examples.

Preparation

The artifacts that you need for the deployment are a DTSX package that defines the work of the plan and a T-SQL script to schedule the job that runs the plan. Do the following to create these artifacts.

  1. First manually create the maintenance plan that you want using SQL Server Management Studio. Open the server node, open Management, right-click on Maintenance Plans and select New Maintenance Plan. Set up the maintenance plan how you want it and then save the plan. See http://technet.microsoft.com/en-us/library/ms191002.aspx for more information on using the Maintenance Plan Wizard.
  2. Export the plan that you created as a file. Connect to Integration Services on the same server. Open the Stored Packages node, then the MSDB node, then Maintenance Plans. Find the plan that you created and right-click on it and select Export Package. Leave the default value of File System for the Package Location. Browse to a folder to save the file. You could name it MyMaintenancePlan.dtsx. See http://technet.microsoft.com/en-us/library/ms141772.aspx for more information about importing and exporting packages.
  3. Modify the maintenance plan that you created to schedule a job. You may have noticed that by default the plan was not scheduled. It could only be run on demand. To do this connect to the database engine and open the Management node and then the Maintenance Plans node. Find the plan that you created and right-click on it and select Modify. Click on the calendar icon in the Schedule column.  Setup the parameters on the  New Job Schedule window and save the job schedule.
  4. Create the T-SQL script by opening the SQL Server Agent node and then the Jobs node. Find the job that you scheduled in the previous step and right-click on it and select Script Job As/CREATE To/File and choose a file name like ScheduleMyMaintenancePlan.sql. Edit the script to remove the @owner_login_name parameter from the sp_add_job procedure so that the job will be created with a default owner on the target server. Also add additional script to create a subplan entry in the MSDB database placed before the commit transaction as follows.
    EXEC @ReturnCode = msdb.dbo.sp_maintplan_update_subplan
    @subplan_id = '{00000000-0000-0000-0000-000000000}',
    @plan_id = '{00000000-0000-0000-0000-000000000}',
    @name = 'My Maintenance Plan',
    @description = 'My Maintenance Plan',
    @job_id = @jobId,
    @allow_create = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    This script is to prevent errors during execution of the maintenance plan caused by the OnPreExecute event in the subplan. Remember to replace the zero guids for @subplan_id and @plan_id with the actual guids generated for your plan and subplan in your DTSX package. Do not attempt to insert into sysmaintplan_plans since it is a view not a table. For more information about using T-SQL to create a job schedule, see http://technet.microsoft.com/en-us/library/ms187320.aspx

At this point you should have two files (MyMaintenancePlan.dtsx and ScheduleMyMaintenacePlan.sql) that you can use for the deployment.

Deployment

For the deployment the goal is to transfer the files to a new server and then install the package and create the job schedule. The installation can be automated using batch files, PowerShell or MSBuild.

  1. Deploy the MyMaintenancePlan.dtsx and ScheduleMyMaintenancePlan.sql files to the new server.
  2. Before installing the package ensure that the SQL Server Agent is enabled and running.
    sc config SQLSERVERAGENT start= auto
    net start SQLSERVERAGENT
    The space in front of auto is important. Use net to start the service instead of sc in order to wait for completion.
  3. Deploy the package to the new SQL Server using dtutil. It is possible to use PowerShell, however it may not be available on the target system due to security. Run the following.
    "$(ProgramFiles)\Microsoft SQL Server\110\DTS\Binn\dtutil.exe" /FILE MyMaintenancePlan.dtsx /DestServer sqlServerName /COPY SQL;"\Maintenance Plans\MyMaintenancePlan"
    For maintenance plans, it is better to install the package directly to the SQL Server rather than going through Integration Services since you may have named instances.
  4. Run a T-SQL script to create the job schedule.
    sqlcmd -S sqlServerName -i ScheduleMyMaintenancePlan.sql -x
    The -x parameter is needed to prevent sqlcmd from interpreting macros that are in the script.
  5. Verify that everything was installed correctly using SQL Server Management Studio.

Lastly ensure that you have scripted these deployment steps so that you can copy the files and the script to another server and then run the script.

Advertisements
Tagged with: , , , , ,
Posted in Development
14 comments on “Automating Deployment of SQL Server Maintenance Plans
  1. Kiran says:

    Was just looking for this. Great help..!!!

    BTW, I followed your steps and able to deploy plans to other servers and also able to create the jobs. The only glitch being, when I opent he maintenance plan ont he destination server, the subPlans schedule still show “Not Scheduled (On Demand)”. But the jobs which are created through the scripts just run fine. Any idea about this???

    • robertbigec says:

      I noticed that as well. When you schedule the job from the maintenance plan editor it knows which scheduled job is associated but I don’t know where it stores that information so I did not make the connection. I am not worried about it though because the jobs work.

  2. darek says:

    This is a great tutorial. Although I have an error.
    When I execute modified job script (with sp_maintplan_update_subplan) I get a message:
    HResult 0x1FB2, Level 16, State 1
    Error converting data type varchar to uniqueidentifier.

    The same is when executed in management studio. Do you know how to resolve it?
    I used this syntax:
    sqlcmd -S server -U sa -P pass# -i “DB maintenance plan.sql” -x

    • darek says:

      I found solution – there is a mistake in you guid string. It should be:
      ‘{00000000-0000-0000-0000-000000000000}’ (3 more zeros at the end)

      • darek says:

        Actually it still won’t work.
        It is needed to replace all zeros fake ids with real id taken from dtsx package.
        DTS:refId=”Package\Reporting Task for subplan-{0B0E2412-2662-40EE-B7EF-695577C1C160\}”

        You should have write about it.

      • robertbigec says:

        Thank you. I’ve edited to note that the guids should be replaced with the actual guids.

  3. Erik says:

    There is a potential issue in this scenario. If you connect from your desktop to a remote SQL Server (usual in larger environments) the SSIS export scripts out the currently connected server (say “JEEVES”) as the local server connection. Once you deploy the script the server the plan will connect to is “JEEVES” even if that server is not the local server at all. So check the “Manage Connections …” dropdown box!

    The workarounds are very simple:
    1. open the .dtsx file and change the server name “JEEVES” to “localhost” wherever you can find it (two occurrences in my case).
    2. always make sure when you connect to “localhost”.

  4. Erik says:

    You should also take in consideration your connection. When SSIS scripts out the job, it will also script your current connection as the “Local server connection”. When you deploy it, it still holds the name of the server you created the maintenance plan on as the Local server connection. For me, when I deployed the package to another machine, it created the backups of the databases on the original machine.

    Using “localhost” as your current connection (as I suggested in an earlier reply, which I now do not see) will help you out if you only have default instances on your machines. If you also use named instances, you should manually change the Local connection to the {hostname}\{instancename} in the DTSX package(s). With modern text editors this is a breeze, but you still have to think of it!

  5. James says:

    Here’s the bit you are missing with reference to “When you schedule the job from the maintenance plan editor it knows which scheduled job is associated but I don’t know where it stores that information so I did not make the connection.”

    DECLARE @scheduleId INT <– ADD THIS
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
    …,
    …,
    @schedule_id = @scheduleId OUTPUT <– ADD THIS

    THen in:-

    EXEC @ReturnCode = msdb.dbo.sp_maintplan_update_subplan
    @job_id = @jobId,
    @schedule_id = @scheduleId, <– ADD THIS
    @allow_create = 1

  6. […] Automating Deployment of SQL Server Maintenance Plans SSIS package query SSIS Package Extract from MSDB How to Transfer(Copy) Maintenance Plans from One Server to other???…. […]

  7. Justin says:

    Can I get the plan id from the dtsx package id GUID within the General report ? I open the General report from SSIS connection in SSMS, right click the SSIS package click report, and click General. Otherwise, how can I determine the right GUIDs ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: