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?
-
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.
From squillman -
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 :
However, i think it's much easier to just script the SQL a package runs instead, as mentioned by squillman.
From CodeByMoonlight -
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.
From Rob Farley -
Rather than using maintenance plans, consider using home-grown stored procedures.
Here's an excellent example.
(And don't shrink your data files).
From SuperCoolMoss
0 comments:
Post a Comment