Post reply

Maintenance Plans - How to Save Copies or Back The Plan Up

  • August 4, 2009 at 12:38 pm

    #203428

    Hi Folks

    I have spent quite a bit of time creating some Maintenance Plans for SQL Server 2005 and I would like to kjnow if there is anyway to back these plans up somewhere so that if one gets trashed I do not have to develop the entire plan all over again. I have tried looking into Integration Services (export package-import package), but evertime I try to make a copy of a plan, it gets trashed. Granted I have limited expertise with it.

    Any input would be appreciated

    Thanks

    Jim

  • August 5, 2009 at 3:50 am

    #1034672

    Maintenance Plans are stored in the msdb database. So take backup of msdb database.

  • August 5, 2009 at 6:06 am

    #1034705

    also, you can script them and source control them.

    Vivek Shukla - MCTS SQL Server 2008

  • August 5, 2009 at 9:25 am

    #1034885

    Thanks Suresh B.

    Jim

  • August 5, 2009 at 9:27 am

    #1034887

    Hi Vivek

    okay..you lost me at "script them"

    what does that actually mean ?

    can you provide an example ?

    Thanks

    Jim

  • August 6, 2009 at 4:49 am

    #1035261

    okay..you lost me at "script them"

    Sorry Jim, i should have been bit more clearer there. What i wanted to say was create jobs in terms of scripts. you can very much script your own maintenance paln.

    Sorry for not being very clear.

    Vivek

    Vivek Shukla - MCTS SQL Server 2008

  • August 6, 2009 at 6:00 am

    #1035295

    also if you wanted to export generated maintenance plan to file system you could do that.

    Vivek Shukla - MCTS SQL Server 2008

  • August 10, 2009 at 9:19 am

    #1036857

    Got it 🙂

    Thanks Vivek

    Jim

  • August 10, 2009 at 1:28 pm

    #1037017

    Here is what I do:

    Create SSIS package in BIDS

    Import Maintenance Plans into new project

    Save

    Now, if I need to reinstall - or even install on a new server - all I have to do is open the project, change the connection information, modify the tasks to make sure they have the correct information (e.g. backup locations, file locations for cleanup tasks, correct databases selected for backups, etc...). Once that is done, perform a save copy as (File | Save Copy As...) and save the plan to the server under the Maintenance Plans folder.

    Once the plan is up on the server, open the plan in SSMS and schedule it. Schedules are not saved - so you have to do this to create the schedule and also to create the SQL Agent job.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • August 10, 2009 at 1:33 pm

    #1037021

    Hi Jeffrey

    i wil llook at this scenario you suggested

    I have already written my plans in SSMS though

    I did some testing the last time with BIDS and I had all sorts of issues with it wiping out my plans when doing exports and imports, but I wil try it again

    Thanks

    Jim

  • August 10, 2009 at 1:38 pm

    #1037027

    No worries!

    Happy to be of help.

    Vivek

    Vivek Shukla - MCTS SQL Server 2008

  • August 10, 2009 at 4:57 pm

    #1037138

    JC (8/10/2009)

    Hi Jeffrey

    i wil llook at this scenario you suggested

    I have already written my plans in SSMS though

    I did some testing the last time with BIDS and I had all sorts of issues with it wiping out my plans when doing exports and imports, but I wil try it again

    Thanks

    Jim

    First, you have to create the maintenance plans in SSMS. Once created, you can then import them into an SSIS project. You cannot create them in BIDS and publish to SSMS as maintenance plans - it does not work that way.

    Second, once the plans are created - you don't add tasks in BIDS. You can modify the existing tasks, but don't try adding tasks or removing tasks as that can cause problems.

    Once again, you modify the connection information (not the name - don't ever change the name of the connection). Then, you can change the task information - for example, for a backup task you would change the databases being backed up and the location. Then, you perform a save copy as and save it up to the server.

    If a plan with that same name already exists, overwriting the plan this way will cause problems. Delete the existing plan first (or rename it) before saving.

    It does work, and works very well - if you stick to the above rules 😉

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • August 11, 2009 at 9:51 am

    #1037538

    Excellent !

    Thanks Jeffrey

    Jim

Viewing 13 posts - 1 through 12 (of 12 total)

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