question

CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 asked Cathyji-msft edited

How to migrate/copy Maintenance Plans to another SQL Server?

I have looked at TRANSFER MAINTENANCE PLANS FROM ONE SQL SERVER TO OTHER and Move SQL Server Maintenance Plan from One Server to Another but both of this how-to instruction involves logging into the Integration Services and the server I'm working do not have Integration Services installed. Is there still another way I can move/copy these maintenance plans to other server without integration services installed?


sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Shashank-Singh avatar image
0 Votes"
Shashank-Singh answered

Maintenance plan are stored as SSIS packages in MSDB so normally you would need Intergration services. I searched and Got This Stackexchange thread where user says to follow ssis-package-extract-from-msdb.html to extract package from msdb and move that to new destination. See if the link is of any help to you. It says to run below query after getting package store location.

 ;
 WITH FOLDERS AS
 (
     -- Capture root node
     SELECT
         cast(PF.foldername AS varchar(max)) AS FolderPath
     ,   PF.folderid
     ,   PF.parentfolderid
     ,   PF.foldername
     FROM
         msdb.dbo.sysssispackagefolders PF
     WHERE
         PF.parentfolderid IS NULL
    
     -- build recursive hierarchy
     UNION ALL
     SELECT
         cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath
     ,   PF.folderid
     ,   PF.parentfolderid
     ,   PF.foldername
     FROM
         msdb.dbo.sysssispackagefolders PF
         INNER JOIN
             FOLDERS F
             ON F.folderid = PF.parentfolderid
 )
 ,   PACKAGES AS
 (
     -- pull information about stored SSIS packages
     SELECT
         P.name AS PackageName
     ,   P.id AS PackageId
     ,   P.description as PackageDescription
     ,   P.folderid
     ,   P.packageFormat
     ,   P.packageType
     ,   P.vermajor
     ,   P.verminor
     ,   P.verbuild
     ,   suser_sname(P.ownersid) AS ownername
     FROM
         msdb.dbo.sysssispackages P
 )
 SELECT 
     -- assumes default instance and localhost
     -- use serverproperty('servername') and serverproperty('instancename') 
     -- if you need to really make this generic
     'dtutil /sourceserver ' + @@SERVERNAME + ' /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /copy file;".\' + P.PackageName +'.dtsx"' AS cmd
 FROM 
     FOLDERS F
     INNER JOIN
         PACKAGES P
         ON P.folderid = F.folderid
 -- uncomment this if you want to filter out the 
 -- native Data Collector packages
 -- WHERE
 --     F.FolderPath <> '\Data Collector'




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 answered Shashank-Singh edited

Seems like the query generates .dtsx packages which I will have to import into the new SQL Server's Integration Services, correct? What if in the this sql server I do not have Integration Services installed either? This procedure then does not, correct?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Did you read the comments in the link, posted by readers, I guess that should have answer to this question of yours

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @CharlieLor-4693,

This only works if you have SSIS fully installed, because dtutil - which this is built round - is basically disabled otherwise, even though it is present.

Suggest you install SSIS feature, follow the steps from the blog that you offered to migrate Maintenance Plans.

Best regards,
Cathy


If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.