Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

copy maintenance plan Expand / Collapse
Posted Monday, August 6, 2012 1:46 PM


Group: General Forum Members
Last Login: Thursday, December 1, 2016 2:45 PM
Points: 2,015, Visits: 3,809
We have quite a few SQL servers in our enviroment. And we setup standard maintenance plan with 5 subplans like backups( include full, diffrential, transaction log), weekly reorg, check db integrity. clean up history etc.

I usually manually created this maintenance plans for each server.

I wonder if there is an easier way to migrate or copy it to new server.

I know I can copy msdb package to the server, but it also need multiple steps, and need to change server connection, I found it saves more time to recreate them manually in ssms than using the import package.

I don't find an option that I can script out these plans.

Post #1340876
Posted Monday, August 6, 2012 2:24 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 25, 2016 10:59 AM
Points: 366, Visits: 997
There are a couple options here. Remember, that since SQL 2005, Maintenance Plans are basically just SSIS packages. The wizard just provides an interface with limited SSIS actions inside SSMS.

1) Create your plan inside BIDS or Visual Studio. This gives you the power of treating it like an actual SSIS package. You can modify the connection objects each time before you deploy to a new server. Of course, this could be a problem since now you need to either copy the package to the server every time you want to update or deploy it. You may also need to load it into Integration Services through SSMS to store it MSDB if that's your thing. The latter part isn't required but I know some people like to manage their packages this way.

2) Instead of using the wizard, create a DBA database on each server. Script out your plan actions as pure TSQL. Create stored procedures with the TSQL inside the DBA database. Run each inside an agent job step. This way, the jobs can be scripted. Once the DBA database is created on each server, you can run the job creation script. Then all you need to do is schedule the job(s) and you're done. The schedules could also be scripted.

The 2nd is probably your best bet if you want something scriptable, portable and very re-useable. If you're not terribly familiar with system objects and/or TSQL this is a good way to hone those skills.

Of course, if your plans are very simple, you may just be better off continuing with the wizard for now. I still suggest you take a look at the TSQL being executed by your plans now to better acquaint yourself with how to execute these tasks with nothing but TSQL.
Post #1340888
Posted Tuesday, August 7, 2012 2:41 AM

Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, March 18, 2016 8:38 AM
Points: 737, Visits: 844
If you haven't already read it take a look at this

I use the MaintenanceSolution.sql on there for just about everything with a few changes here and there
Post #1341067
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse