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.
| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2010-10-08 : 13:46:33
|
| We have set up a new Server. It was easy to move the Databases over to the new server - just backup and restore.But what about moving Server Objects to the new server? In particular Maintenance Plans and Jobs. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-10-08 : 19:53:30
|
| The jobs can be scripted out and moved. I don't use Maintenance Plans, so I'm not sure how to move/script them. Now might be a good opportunity to re-do them as SQL scripts instead ;-)=======================================In all life one should comfort the afflicted, but verily, also, one should afflict the comfortable, and especially when they are comfortably, contentedly, even happily wrong. -John Kenneth Galbraith, economist (1908-2006) |
 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-10-09 : 13:50:35
|
| As previously stated, agent jobs can be scripted. Using Object Explorer details, select all of the jobs you want to script - right click and select the option to generate scripts. Do not include the jobs that are created by the maintenance plans, as those will be recreated when you create new maintenance plans on the new server.Maintenance plans cannot be scripted, but there is a method of moving them from one server to another. It is rather involved, but once you have it defined - you can create basic maintenance plans on any server in a few minutes.1) Create a new SSIS project in BIDS2) Delete the default Package.dtsx that is created - you won't need it.3) Right-click on the Packages folder in the Solution Explorer a) Add Existing Item b) Select SQL Server as the store c) Put in the old SQL Server to get the maintenance plans d) Click on the elipsis to identify the package to be imported - select the first maintenance plan e) Repeat for each maintenance plan4) Modify the connection string in the packages to point to the new server a) Do not try to add any objects b) You can change each task to assign the databases, backup paths, etc...5) Save the package to the new server a) File | Save Copy As b) Save the package to the new server in the maintenance plan folder6) Optional: update the maintenance plan owner a) This has to be done with an update statement (see following)7) Open Maintenance plan in SSMS on new server and create a schedule a) Validate the plan b) Create a schedule - this will create the agent jobsI change the owner of the maintenance plans because we don't want agent jobs running with a users login. This is easy to do with a simple script:Use msdb;Declare @owner varbinary(85);Set @owner = 0x01 -- sa Update dbo.sysdtspackages90 Set ownersid = @owner Where packagetype = 6; -- 6 = Maintenance PlansGoYou'll get a lot of recommendations to not use maintenance plans and to use SQL scripts instead. Either way works - I prefer using maintenance plans myself, and I have not found anything that scripts can do that I cannot do with maintenance plans.Jeff |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-10-12 : 09:30:34
|
| Thankyou. |
|
|
|
kat2me
Starting Member
1 Post |
Posted - 2011-01-26 : 14:26:44
|
| Hi Jeff,I am right there with you until step 5b. Since I always thought the main plans were stored in the msdb, I'm not sure what folder to place them in on my new server.Background: We have one server running sql 2008r2 with SSIS running. I wanted to take the default main plans and put them on our new instances of 2008r2. Seems like we are putting up new instances every week and it is time consuming and tedious to recreate the plans on different instances. So that is where I thought you idea was great.Thanks for any help.PS. I used the transfer jobs task in SSIS to move over jobs. It is incredibly simple and reliable. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-01-26 : 16:39:49
|
| When you use File | Save Copy As, select SQL Server - put in the new server name, and select the maintenance plans folder on that server.This is really just the reverse of pulling the maintenance plan from SQL Server into your SSIS package.Jeff |
|
|
|
|
|
|
|
|
Move Server Objects between Servers