Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
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 '09 at 20:14
    
Which version of the "Sql Maintenence Wizard" are we talking about here? –  Wyatt Barnett Sep 18 '09 at 20:15
    
I just need to shrink, reindex etc. periodically. Isn't that what the maintenance is for? –  Ron Harlev Sep 18 '09 at 20:16
    
I'm using SQL 2005 –  Ron Harlev Sep 18 '09 at 20:16

4 Answers 4

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.

share|improve this answer

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).

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.