Question: Trying to set up Maintenance Plan through T-SQL

Question

Post reply

Question: Trying to set up Maintenance Plan through T-SQL

Brian Munier

SSCrazy

Points: 2875

April 11, 2006 at 7:55 am

#112635

I realize that there are several places this thread could be started, so this is my best guess.

Where I work we a attempting to deploy an application that uses SQL Server. We are trying to code as many of the SQL Server steps as possible.

So the fundamental steps are: 1. Install SQL Server, 2. Create the database, schema, initial data etc., 3. Create maintenance plan.

SQL Server has stored procedures such as: sp_add_maintenance_plan, sp_add_maintenance_plan_db, one for jobs, etc. Now Microsoft on MSDN says:

sp_add_maintenance_plan (Transact-SQL)

Adds a maintenance plan and returns the plan ID.

Note:

This stored procedure is used with database maintenance plans. This feature has been replaced with maintenance plans which do not use this stored procedure. Use this procedure to maintain database maintenance plans on installations that were upgraded from a previous version of SQL Server.

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

And of course makes no suggestion on how to create a maintenance plan without using enterprise manager. We would prefer to code the Maintenance plan creation to increase speed and accuracy of deployment.

Question: Does any one know if there are better procedures, or methods to create a maintenance plan using T-SQL. Also does anyone know if SQL Server 2005 still supports these stored procedures? (I will also ask on a 2005 forum.)

Thanks in advance, Brian

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 1

The sqlmaint.exe utility and the msdb tables that support SQL 2000 maintenance plans are going away. SQL 2005 includes the stored procs you mention, but the tables are there only if SQL 2005 was installed as an upgrade. A new SQL 2005 installation won't have the tables that those stored procs use.

SQL 2005 maintenance plans are implemented in SSIS tasks. You can automate the creation of maintenance plans, but probably not in T-SQL. Look at the "Working with packages programmatically" topic under "SQL Server programming reference" - "Integration Services programming" in BOL. The quickest way to figure it out might be to create a maintenance plan using the wizard, then write code to load the package and explore the objects in it. This should give you enough information to write code to create new packages on other servers. You may need to throw in some SMO code to create SQL Agent jobs to run the maintenance packages.

Brian Munier SSCrazy Points: 2875 More actions · Answer 2

Thank you for the information Scott. I figured that there was probably a new way to get to it, but the BOL did not make it obvious.

Thanks again,

Brian

Kyle Schlapkohl SSCrazy Points: 2463 More actions · Answer 3

I'm sorry, I'm a new DBA and am having trouble following the steps to fix the problem. Could you please go more into detail on how to get around the SP problem? Thank you for any help in advance.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

You "get around the SP problem" by realizing that the SPs are going away and you have to learn how to work with the new SSIS-based maintenance plans. You build them graphically in the SSIS IDE, they can't be scripted in T-SQL. If you're thinking "Oh yeah? What about object automation routines, or a CLR stored proc that does ..." then you know enough to go off and do that without any advice from me.

If you want to do something remotely like scripting a maintenance plan, you have to leave SQL Server and use a programming language to use the Microsoft.SqlServer.Dts methods to create a package programmatically. Or you might try to create a maintenance plan the normal way, but include some clever scripting tasks that modify the runtime behavior so it intelligently does what you want it to even when deployed on multiple servers.