Creating a Maintenance Plan via the Command Line

Hello All,

I've searched high and low for documentation on this to no avail.

Basically my goal is to create a maintenance plan in SQLSERVER2005 via the command line. I need to create this plan in a way that it can be seen in the list of Maintenance Plans in the Management Studio Interface. I went into the SSIS designer and created my plan. I now have a DTSX file. I tried the dtutil.exe utility, however i never saw my maintenance plan in the list of plans.

I ran dtutil.exe and did /FILE to /SQL but i don't see the plan listed or in a way a user could modify it, which is of the utmost importance to my clients.

How do i get my file to turn into a real running Maintenance Plan that is seen in the list of Maintenance Plans via the Management Studio Interface and is editable by clients?

Other things to keep in mind, i'm attempting to create these via Installshield MSI installer. So i need to do it via command line, or file system-wise. No interface or user interaction.

Please Adivse.

Hi Brendan,

You can use the stored procedures sp_add_maintenance_plan, sp_add_maintenance_plan_db and sp_add_maintenance_plan_job to create maintenance plans, add the databases to it and associate the jobs with the same. Refer documentation on these from Books Online.

Thanks,
Kuntal

|||You have to take help of SQLCMD in this case where using the statements referred above as a Stored procedure, this will do when you want to deploy as the package.|||These three SP's i understand. But how do i reference the file? Or how do i give the plan the 'meat' of what it needs to do? From what i can tell i went to the designer created my dtsx file, and that code in that file need to be input somewhere... can that be done through a sproc as well?|||Anyone?|||

BOL states:

The sqlmaint utility performs a specified set of maintenance operations on one or more databases. Use sqlmaint to run DBCC checks, back up a database and its transaction log, update statistics, and rebuild indexes. All database maintenance activities generate a report that can be sent to a designated text file, HTML file, or e-mail account. sqlmaint executes database maintenance plans created with previous versions of SQL Server. To run SQL Server 2005 maintenance plans from the command prompt, use the dtexec utility utility.

|||

Yeah i wish i could run it like that, but i need it to run in the exact fashion as if it were created in the GUI. Maybe i should explain this better...

I need a programatic way, either through sprocs or command line, to create a maintenance plan, that will show up in the GUI, under maintenance plans, with my name, and have all the steps i specify in the design screen.

Above i see that i run these;

sp_add_maintenance_plan : adds my plan. ok great.

sp_add_maintenance_plan_db : adds a databse that i want to run the plan against, ok great.

sp_add_maintenance_plan_job : ok, adds a job to run my plan. great.

But where does the 'meat' , the steps i designed in the SSIS designer go? all i want to do is add a new maintenance plan that shows in the GUI and has all my steps in the plan. No the steps in the job, the steps in the plan.

I'm sorry, for some reason i cannot wrap my head around this. Thanks for your patience. But i'm not a SQLSERVER admin, i'm an install guy. Any further help would be greatly appreciated.

-b

|||

You can create the SSIS package to perform this maintenace task and you can deploy that SSIS package to multiple servers, http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx#ERGAE fyi.

Drop me an email using my site (contact us) below and I can talk you through the steps.

|||Emailed...|||Anybody have a solution to this? I'm sure it's quite simple, there is just no direct example or i'm not doing this correctly. I'm getting rather desperate here... any help would certainly be appreciated.|||

Brendan

Sorry I didn't get any email from you, could you please send it to smaster@.sqloogle.co.uk.

Brendan Stewart wrote:

Emailed...