How to transfer Maint Plans (chkdb,reorg & so on) from one server to other

Question

BIDS Tx. Jobs task doesn't work as maint plans don't get tx. to the target MSDB db. I have had issues in importing packages to the target server, Is there any other way, I have got 4 servers where I need to tx. them to, & thought if there is a easier way

Answer 1

I believe you are using SQL Server 2005 or a higher version. In such a case, the Maint Plans are SSIS Packages which can be exported after connecting to Integration Services.

Here is a post on the same:
http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/52d71331-908d-4d5e-a14a-c665ce4cbc6a/

Verify that the connectiong string in the package is pointing to the new instance before importing the package. In case you are using custom tasks, then you will have to modify them after you import them to your new instance.

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Answer 2

Thanks for your reply Amit.

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/e4922b02-0c9c-482e-93fd-11aa1613b6d3

Is my thread where I am having issue in importing packages. So that option is gone currently. Other options in the thread that you provided don't work for me.

Answer 3

Here is the answer from that thread:

You can import and export maintenance plans. Connect to Integration Services on the server where the maintenance plans are located. Go to Stored Packages -> MSDB -> Maintenance Plans. From there you can right click on Maintenance Plans and select Import. Or if you right click
on one of your maintenance plans, you can select import or export.

Note: Verify that the connectiong string in the package is pointing to the new instance before importing the package. In case you are using custom tasks, then you will have to modify them after you import them to your new instance.

For a more detailed post, this is what I found recently:
http://www.symantec.com/connect/articles/progress-creating-and-managing-sql-server-database-maintenance-plan

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Answer 4

Thx for replying again Amit, but as I said before I am having issues in import:

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/e4922b02-0c9c-482e-93fd-11aa1613b6d3


I will try dtutil utility later on.

Answer 5

Could you post any error messages that you are receiving?

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Answer 6

Hi Amit,

Pls have a look at my thread,

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/e4922b02-0c9c-482e-93fd-11aa1613b6d3

Answer 7

Do you have any non-MS providers on the machine which can be found under the Providers drop down list in SSMS?

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Answer 8

Where exacly am I looking at in SSMS?

Answer 9

Could you list out the providers that you see when you do the following:

Expand Server-> Server Objects -> Linked Servers -> Providers

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Answer 10

There are many providers listed there, how do I paste a snagit here, what exactly are you looking for?

Answer 11

Could you paste the output after executing the following XP from a Query Window: xp_enum_oledb_providers?

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Answer 12

SQLOLEDB
DTSPackageDSO
SQLReplication.OLEDB
MSOLAP
MSDMine
OraOLEDB.Oracle
ADsDSOObject
IDMObjects.DBDataSource
SQLNCLI
MSDASQL
Sybase.ASEOLEDBProvider
Microsoft.Jet.OLEDB.4.0
MSDAOSP
MSDAORA
SQLXMLOLEDB
SQLXMLOLEDB.4.0
MSIDXS

Answer 13

I know I should have asked these questions earlier:

1. Are you having issues with Maint Plan Package Import or with other packages as well?
2. Are the both Integration Services on the same version and platform (x86/x64)?

I see that you are using Oracle Drivers also on the box. If you are using 64-bit Oracle drivers, then could you check if the steps mentioned in the link below helps:
http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=11

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Answer 14

1. Right Click-MSDB-Import Package , gives error, so its with any kind of pkg, I have asked you 3 times in this post to look at my thread.

2.  Yes

I have no idea where you are going with the Oracle thing. The link you provided has NO relation to the issue!

Answer 15

I did read your post on the other forum. Which is why I asked if you have a 64-bit Oracle driver in use. When you click on import we call an API which loads the provider responsible for importing the package. If a registration with a provider is incorrect, then you could run into the issue due to which the child import dialog box fails to open up. I have seen such issues in the past.

Could you re-register the DLLs present in your providers folder?
Also, unregister and re-register the MsDtsSrvrUtil.dll from the <system drive>:\Program Files\Microsoft SQL Server\90\DTS\Binn folder.

Also, have you tried this from another SSMS client on another box or does that give you the same error?

HTH

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Answer 16

The box is 32 bit with 32 bit SQL Server. It also has Oracle & Sybase installed on it, I don't know how to check if they are 64 bit, I am guessing not. 

Could you re-register the DLLs present in your providers folder?
How do I go about doing that?

Also, unregister and re-register the MsDtsSrvrUtil.dll from the <system drive>:\Program Files\Microsoft SQL Server\90\DTS\Binn folder.
Need steps.

It gives the same error from a different box or SSMS client.

Answer 17

If it's a 32-bit box, then you don't need to worry about 64-bit version of the DLLs.

On the server, where you have the SQL instance installed, run the following commands from the command prompt:

Run:
regsvr32 <system drive>:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvrUtil.dll

For the provider DLLs, navigate to the folder from Command Prompt: C:\Program Files\Common Files\System\Ole DB
Register the DLLs using the following command:
regsvr32 /s C:\Program Files\Common Files\System\Ole DB\<dll file name>

---

This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL

Answer 18

Hi Amit,

I re-registered all the dlls in the above 2 locations, restarted IS, nogo, same error.