Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Maintenance Plan Portability

Maintenance Plans should be scriptable, deployable and maintainable without the need to manually create/update a plan on each server/instance. Current functionality requires the DBA to manually generate a SQL Maintenance Plan one at a time for each instance in the environment. Built in SQL Maintenance Plans should be scriptable, deployable and maintainable with TSQL or in some automated fashion that will allow deployment to large enterprise environments that do not require the use of a GUI.

60 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

Adrian shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

7 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...
  • Anonymous commented  ·   ·  Flag as inappropriate

    I agree we need something in-house instead of using 3rd party products just to ensure the DB is reliable and recoverable.

  • JediSQL commented  ·   ·  Flag as inappropriate

    I got a few up-votes in Connect for this idea, but I can't find it now.

  • GUN10 commented  ·   ·  Flag as inappropriate

    I would reedit this connect item as follows (under the expecations when i started in MSSQL business 17 years ago and the epic community solutions didn 't exist - yet):

    - Microsoft: if you want to keep the "create maintplans stored procedures" code hidden from us. Please make Olas or tigertools Maintenance Solution available in the Toolbox with all its params and best practice advice

    That would be a great leap in DB Maint and DBA community collaboration

    Another one hand-in-hand: Please do not cut off any (job logging) content and replace it by '... job failed'. Not one developper does do this any more in the year 2018! Uups perhaps that would be its own connect item - sorry.

    @adrian: agree to scriptable, deployable and maintainable with TSQL

  • Mighty-O commented  ·   ·  Flag as inappropriate

    The Maintenance Plans are SSIS packages. You can export and import the plan via connecting to SSIS. The exported file is an xml file and you have options to encode sensitive details by password on the extract. The only issue I have with this method is scheduling, e.g. you need to script the schedule addition to run the package (or manually implement the schedule creation).

    Some details on export: https://docs.microsoft.com/en-us/sql/integration-services/service/package-management-ssis-service

    Hopefully that helps.

  • Ra Osolage (sqlkiter) commented  ·   ·  Flag as inappropriate

    @Adrian, we've been wanting this for decades. :-)

    If, by chance, this request gets squashed/ignored (again), take a look at the Ola Hallengren maintenance solution. I think you'll probably like it better than maintenance plans. Haven't met a person who didn't.

  • Joshua commented  ·   ·  Flag as inappropriate

    Great idea Adrian! It's such a pain to re-create maintenance plans on new server builds. Being able to script them out would certainly speed up the process and allow for some fine tuning.

Feedback and Knowledge Base