Post reply

Creating a maintanance plan using a script

  • 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

  • May 14, 2008 at 10:06 am

    #815360

    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

  • May 14, 2008 at 1:50 pm

    #815520

    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

  • May 14, 2008 at 2:25 pm

    #815537

    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

  • May 16, 2008 at 8:43 am

    #816545

    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

  • May 19, 2008 at 6:13 am

    #817115

    Thanks so much!!!

  • July 7, 2008 at 6:35 am

    #838798

    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.

  • July 8, 2008 at 7:13 am

    #839464

    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.

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

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