4

Is there a way to generate a SQL server maintenance plan that will be the same as the one generated by the wizard, but without going through the wizard dialog. I need something that can be created automatically as part as an install process.

Also, it looks like what the SQL wizard is generating, is specific to the current schema (for example for re-indexing tables). Will the wizard generated plan break on any schema change? If so, is there a way to update it, other that regenerating the whole thing again?

4
  • Can you give us a little more detail about what your app will be doing? I'm not sure Maintenance Plans are they way to go here.
    – squillman
    Sep 18, 2009 at 20:14
  • Which version of the "Sql Maintenence Wizard" are we talking about here? Sep 18, 2009 at 20:15
  • I just need to shrink, reindex etc. periodically. Isn't that what the maintenance is for?
    – Ron Harlev
    Sep 18, 2009 at 20:16
  • I'm using SQL 2005
    – Ron Harlev
    Sep 18, 2009 at 20:16

4 Answers 4

2

There's no way (that I've ever seen) to create a maintenance plan without going through either the wizard or the the designer. It's not possible using SMO. You could create an SSIS package that will perform your maintenance plan tasks and deploy that during your installer. Based on your comments above, though, I would suggest writing a couple stored procedures that will perform your tasks. Anything you can do with maintenance plans, you can write TSQL for.

2

Rather than using maintenance plans, consider using home-grown stored procedures.

Here's an excellent example.

http://ola.hallengren.com/

(And don't shrink your data files).

0

Maintenance plans in SSMS are stored as SSIS packages, so aren't scriptable. But you can store them as files and in theory import them into an instance of SQL Server :

http://social.msdn.microsoft.com/forums/en-US/sqlsmoanddmo/thread/ac65cef1-22ee-454f-9947-071a343a0cd9/

However, i think it's much easier to just script the SQL a package runs instead, as mentioned by squillman.

0

I would recommend having a set of SSIS packages that do your maintenance plan stuff - there are tasks do that in the Toolbox. Store them on a shared network drive.

Have them all use the same connection manager. Then, it's extremely easy to execute the maintenance plan style package, changing the connection manager's instance name with the /set option. You can run the package from the command line, from SQL Agent, wherever you like.

As well as doing this, put them on a USB stick on your keyring, so that you can always have them handy.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .