Creating a maintanance plan using a script

Question

Creating a maintanance plan using a script

Nili S

Right there with Babe

Points: 795
More actions
May 14, 2008 at 6:08 am

#154349

Hello.
I'm trying to find information on the web on how to write a script that creates a maintenance plan for my database. I want to create a script that will create a maintenance plan and will run with the rest of my schema creation scripts at the client site.
It seems like MS did not aim for that possibility, and I'm having troubles finding the way to create that sort of script. Can anyone help?
Thanks
Nili

Viewing 8 posts - 1 through 7 (of 7 total)

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

While sp_add_maintenance_plan and sp_add_maintenance_plan_db still work in SQL 2005, they'll be removed in a future version (maybe SQL 2008?) so MS recommends using the interface in SSMS to create them. I guess you could use them keeping in mind that they might not work when you upgrade SQL Server.

Greg

Sameer Raval Hall of Fame Points: 3214 More actions · Answer 2

You can write your own SSIS where you define various task, i.e. backup db, remove db file from specific folder etc.

basically this is what Maintenance Plan does behind the scene.

Or you can write a script with cursor etc. to get task done what you do thru MP.

I am not sure why someone need to spend time writing script for Maintenance Plan, MPlan in itself is readymade GU for the ones who are not willing to write scripts or there SSIS

Kindest Regards,

Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 3

I have a set of Maintenance Plan - like stored procedures that you are welcome to use. They have features for backup, integrity check, index rebuild, index reorganize and update statistics.

http://ola.hallengren.com/sql-server-backup.html

http://ola.hallengren.com/sql-server-integrity-check.html

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Ola Hallengren

http://ola.hallengren.com

tass99 Grasshopper Points: 13 More actions · Answer 4

Any ideas as to how I could script out an existing maintenance (set up through Studio UI), so that I could roll this out to multiple servers without using the UI?

Thanks

Nili S Right there with Babe Points: 795 More actions · Answer 5

Nili S

Right there with Babe

Points: 795

May 19, 2008 at 6:13 am

#817115

Thanks so much!!!

Yuri Nogin Grasshopper Points: 11 More actions · Answer 6

Guys,

I was looking at ways to script Maintenance task and distribute them to many servers automatically instead of creating them through UI or MSDB backup / restore. To me the easiest and quickest way is to

1) create a set of Maintenance plans on one instance

2) Export (store) them as DTSX files on the file system (instead of MSDB - default)

3) Modify DTSX packages (add global variables to maintain connection info). Add INI file (or whatever your preference) to change global variables for the specified instance

4) Copy files onto destination instance (server)

5) Import DTSX files into Maintenance Folder on Integration Services

That's it.

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 7

I think Yuri Nogin has it right; to create an easily deployable Maintenance plan create an SSIS package. The one amendment I would have is that I would leave the variables internal to the package, create a script to create SQL Server Agent Jobs to launch the individual maintenance packages (on each server) and set the variables there.