Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 How to migrate/copy Maintenance Plan?

Author  Topic 

Martyn123
Starting Member

29 Posts

Posted - 2011-01-04 : 08:02:00
Hi,

I have created one maintenance plan which performs backups,index rebuilding,stats maintenance task.We have 8 servers and I don't want to go through all the same procedure of setting up maintenance plan using wizard for all other 7 servers.Is there any other way of doing this; as I have read that maintenance plan script can't be generated.

Please help me out in this regard.

Thanks.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-04 : 08:34:12
As far as I know the only way to do this is to create a "standard" SSIS package instead of a maintenance plan. All the same objects (like backup, index rebuild, etc) are available in the toolbox when you create a new SSIS package in Business Intelligence Development Studio.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-04 : 08:35:17
...and you can save the package to a file on the filesystem and then copy it to your different servers.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-01-04 : 16:21:44
quote:
Originally posted by Lumbago

...and you can save the package to a file on the filesystem and then copy it to your different servers.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com




This will not work, unless you modify the file and change the local connection to the new server.

The easiest way to do this is:

1) Create a new SSIS project in BIDS
2) Add Existing Items to the project - select SQL Server and navigate to the instance where the plans reside.
3) Change the connection information for package
4) Change the selected databases and path information (note: don't change anything else, or it won't work)
5) Perform a Save Copy As and save the package to the new server
6) Optional: Update the owner of the package (manually update the tables in MSDB that contain the package definition)
7) Open each package using SSMS on the new server, verify everything is the way you want it and schedule it.
8) Repeat for each server.

Note: be very careful with changing anything in the packages in BIDS. Do not add tasks or remove tasks, or change anything that is not something that can be changed in SSMS. If you do, the package may not work.

Also, once saved to the server - don't try to save again without deleting the previous package.

Jeff
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-01-04 : 17:13:10
Blimey! Sounds like hard work ... when is MS going to make the Maintenance Plan Wizard a Swan rather than an Ugly Ducking I wonder ...
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-05 : 04:07:45
Another alternative is to do the maintenance work "manually" (schedule scripts with sql server agent) using custom scripts. This is probably what most DBA's do and will provide you with more flexibility and better control of what's going on, especially when something goes wrong.

SQL Server agent jobs can be scripted very easily and deployed to numerous servers. It is also possible to have one master sql agent server that runs jobs on several other servers but I've never done this (right-click the agent in SSMS -> choose Multi Server Administration).

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-01-05 : 04:25:09
"This is probably what most DBA's do"

I'm one of them ... the way I look at it is that when MOST DBA's move to use Microsoft's Maintenance Wizard THEN the Wizard will have become a Swan
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-01-05 : 11:01:58
I'm not one of them - but then again - I do not use the Wizard. There is absolutely nothing that can be done with agent jobs and scripts that I cannot do with maintenance plans.

I have a project in BIDS where I keep a default maintenance plan setup for my systems. When I create a new system, I open BIDS - open the default maintenance plans, change the local connection and save a copy to the new server. Open the plan in SSMS, verify the databases to be backed up are selected and schedule the plan. Takes no more than 10-15 minutes to set up a new set of maintenance plans on a new server.

If the system is large enough (e.g. 100GB+), I have a plan that uses custom reindexing and update statistics. And for the really large systems, I have plans that use Litespeed.

All of my plans also use custom code to remove old backup files - instead of relying on the default task which is not flexible enough for me.

I see the biggest problem is not a problem with maintenance plans or agent jobs - it is that the person setting up the plans does not understand what needs to be done, so they select everything (wizard), or they don't do enough (scripts).

Jeff
Go to Top of Page

chuckh1958
Starting Member

2 Posts

Posted - 2011-09-12 : 15:29:32
quote:
This will not work, unless you modify the file and change the local connection to the new server.

The easiest way to do this is:

1) Create a new SSIS project in BIDS
2) Add Existing Items to the project - select SQL Server and navigate to the instance where the plans reside.



How do you "select SQL Server and navigate to the instance where the plans reside"? When I select "add existing item" the only choice I'm offered is files on the local machine. I'm using SQL 2008 R2 and the VS 2008.
Go to Top of Page

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2011-09-12 : 15:58:52
To move Maintenance plans from one server/instance to other:

Open SSIS
Go to MSDB
Select maintenance plan and select export to file system from source server.
Connect to destination ssis server
Go to MSDB
Right click Maintenace Plan and select import package.
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-09-12 : 22:57:57
quote:
Originally posted by Peter99

To move Maintenance plans from one server/instance to other:

Open SSIS
Go to MSDB
Select maintenance plan and select export to file system from source server.
Connect to destination ssis server
Go to MSDB
Right click Maintenace Plan and select import package.




If you do this - the maintenance plan on the new server will run against the old server because the connection is saved inside the SSIS package. You have to modify the SSIS package and change the local connection to the new server.

Chuck - the dialog box that opens when you choose Add Existing Item in the SSIS package should have three options. The first option normally is SQL Server. The other options are File Store and File System. I don't know why you wouldn't have those options available - I've never seen that issue before.

Jeff
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-13 : 00:12:02
"There is absolutely nothing that can be done with agent jobs and scripts that I cannot do with maintenance plans"

I'm shocked you use maint plans Jeff. I agree that the biggest problem with them is the users, but then again, they are a beginner tool -- so ya get what can be expected with 'em. But the user isn't the only problem. They simply are not as flexible as writing your own scripts/jobs.

And if you're creating multiple plans to handle a bunch of custom code and various schedules, then your clogging up the works with less maintainable junk that takes longer to create, troubleshoot, and deploy.

It only takes an experienced DBA a few minutes to deploy his/her maintenance jobs too.
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-09-13 : 16:06:56
Well, yes I do use maintenance plans and I don't seem to have any of the issues you hear about. I like the way they are organized and I can see what tasks are being executed in each sub-plan.

It is also very easy to set this up for someone who isn't an experienced DBA so they can manage their systems. I have a lot of systems where non-DBA's need to support them and putting a bunch of scripts in to perform maintenance sounds like a good plan but doesn't work very well in reality.

The visual representation allows those not familiar with the processes to see exactly what is being done - and when, and the dependencies.

At this point, it is personal preference - because I can just as easily create agent jobs to do everything. However, with agent jobs we end up with lots of jobs, with multiple steps that are not as visible as a maintenance plan.

Jeff
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-14 : 02:40:53
It's all a matter of personal preference...always. The problem I have with maintenance plans is the standard error logging. It's often very verbose and doesn't really tell you anything sensible, at least not in the sql agent job step which is always the place I look for errors. I guess adding error log files to each job step could be an option or to add logging to a table inside the package (this is possible in maint.plans as well right? not only in "standard" ssis-packages?) but I just find it better to use custom scripts. They are always the same and I know them by heart so I can easily do whatever I want with them...

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-09-14 : 03:17:31
We have an Administrative database. We RESTORE a backup to new servers, and run an Sproc that clears all the tables and schedule a single task to call an Sproc at the minimum interval that backups are required (e.g. every 15 minutes).

The 15-minute-task:

1) Looks for new databases it has not seen before. If any are found it adds them to the Task List using the highest-care-level settings - e.g. Daily Full backup, TLog backup every 15 minutes. If the database name starts with "RESTORE_" it ignores it (but such a database name can be added to the Task list manually). This stops LIVE databases that we restore for some diagnostic purposes from immediately starting to make a full backup!

2) It launches all tasks that are overdue, and marks-forwards their next execution time.

The "Type of maintenance" can be set in the table-of-databases. We just have types 1,2,3, ... (in fact we only have two "High care level" and "Low". More could be added).

"High" will make a Tlog backup every 15 minutes. If the database is not in Full Recovery Model then it doesn't attempt to do it, and doesn't raise an error. If the database changes to become Full Recovery Model then it will start making TLog backups. Ditto if a database becomes Single User, Offline, Read Only etc.

Index rebuilds are performed during defined quiet periods. We only rebuild indexes on fragmented tables, and we use Reorganise rather than Reindex on large indexes (I forget the cutoff, its something like >100 pages). The Index Rebuild task increases the frequency of TLog backups to every 2 minutes to prevent NDF growth.

So basically our DEVs and Clients don't do anything. They just create databases when they need them (and, in the main, those databases get the Type-1 every-15-minute-TLog-backup regime).

Personally I think that is what the MS Wizard should do. Backup and maintain a database to a "high data care level" unless told to do otherwise.
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-09-15 : 15:19:58
quote:
Originally posted by Lumbago

It's all a matter of personal preference...always. The problem I have with maintenance plans is the standard error logging. It's often very verbose and doesn't really tell you anything sensible, at least not in the sql agent job step which is always the place I look for errors. I guess adding error log files to each job step could be an option or to add logging to a table inside the package (this is possible in maint.plans as well right? not only in "standard" ssis-packages?) but I just find it better to use custom scripts. They are always the same and I know them by heart so I can easily do whatever I want with them...

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/



Maintenance plans log their history in a different table. Looking in SQL Agent only gives you the execution status of the agent job which is just running the SSIS package. To look at history for maintenance plans, you need to view the maintenance plan history.

I find this logging more than acceptable - and can easily see in the history viewer how long each task took, start/stop times, etc...

Since I work with a lot of people who are not DBA's, don't know T-SQL very well (if at all) - but still have to manage their systems, maintenance plans work very well for them. The visual representation is easy for them to understand - even if I set them up with custom code for index rebuilds/integrity checks/backups, etc...

For example, I have an individual who's primary responsibility is as a business analyst for the application. Since they don't have a DBA (and I cannot be their DBA - different department/system), trying to walk the individual through the code, agent jobs, scheduling and all that goes with that doesn't work very well. Using MP's gives them the visuals they need to understand what is happening a lot easier than a bunch of scripts.

Jeff
Go to Top of Page

mmoore225
Starting Member

1 Post

Posted - 2013-01-04 : 15:55:10
quote:
Originally posted by Kristen

We have an Administrative database. We RESTORE a backup to new servers, and run an Sproc that clears all the tables and schedule a single task to call an Sproc at the minimum interval that backups are required (e.g. every 15 minutes).

The 15-minute-task:

1) Looks for new databases it has not seen before. If any are found it adds them to the Task List using the highest-care-level settings - e.g. Daily Full backup, TLog backup every 15 minutes. If the database name starts with "RESTORE_" it ignores it (but such a database name can be added to the Task list manually). This stops LIVE databases that we restore for some diagnostic purposes from immediately starting to make a full backup!

2) It launches all tasks that are overdue, and marks-forwards their next execution time.

The "Type of maintenance" can be set in the table-of-databases. We just have types 1,2,3, ... (in fact we only have two "High care level" and "Low". More could be added).

"High" will make a Tlog backup every 15 minutes. If the database is not in Full Recovery Model then it doesn't attempt to do it, and doesn't raise an error. If the database changes to become Full Recovery Model then it will start making TLog backups. Ditto if a database becomes Single User, Offline, Read Only etc.

Index rebuilds are performed during defined quiet periods. We only rebuild indexes on fragmented tables, and we use Reorganise rather than Reindex on large indexes (I forget the cutoff, its something like >100 pages). The Index Rebuild task increases the frequency of TLog backups to every 2 minutes to prevent NDF growth.

So basically our DEVs and Clients don't do anything. They just create databases when they need them (and, in the main, those databases get the Type-1 every-15-minute-TLog-backup regime).

Personally I think that is what the MS Wizard should do. Backup and maintain a database to a "high data care level" unless told to do otherwise.



Kristen-

Would you be willing to share the code of your process? This sounds like and excellent setup.

Thanks in advance.
Go to Top of Page
   

- Advertisement -