Post reply

Restore DTS to a different instance or server

  • August 26, 2010 at 5:04 pm

    #237247

    I have a client who somehow ended up with a corrupted master database. Before we did anything I made copies of everything and then proceeded with RebuildM as oulined here: http://msdn.microsoft.com/en-us/library/aa197950%28SQL.80%29.aspx

    After letting it run overnight it became apparent that the utility was never going to finish.

    We decided to set up a new SQL Server instance on some spare hardware and attach the databases they needed for their applications on that. We installed SQL 2008 and that is running fine.

    We had quite a bit of custom DTS on the server that crashed. Since I have a clean copy of their MSDB database, how would I go about getting at the packages? Ideally we'd like to rewrite everything in SSIS eventually but to do that I'll still need to reference the original DTS. I've attached the MSDB database to a SQL 2000 instance here in our shop but I'm not sure how to get at those DTS packages.

    I know they're in sysdtspackages but I need to get them out as .dts files so i can, at the very least, run them from the file system on the new server.

  • August 26, 2010 at 5:11 pm

    #1212632

    SQLDTS.com has lots of info about DTS packages. I would start there.

    http://www.sqldts.com/204.aspx

    You can use DTSBackup from the site also to convert packages to structured storage files.

    http://www.sqldts.com/242.aspx

  • August 26, 2010 at 5:12 pm

    #1212633

    This should work fine for you. This will copy over the most recent version of the DTS package. I recently used it for several hundred packages.

    SELECT T1.*

    Into DTSPackageTransfer

    FROM dbo.sysdtspackages AS T1

    INNER JOIN (SELECT [name], [id], MAX([createdate]) AS [createdate]

    FROM dbo.sysdtspackages GROUP BY [name], [id]) AS T2

    ON T1.[id] = T2.[id] AND T1.[createdate] = T2.[createdate]

    ;

    Insert into msdb.dbo.sysdtspackages (name,id,versionid,description,categoryid,createdate,owner,packagedata,owner_sid,packagetype)

    Select name,id,versionid,description,categoryid,createdate,owner,packagedata,owner_sid,packagetype

    From msdb_bak.dbo.DTSPackageTransfer

    Where Description <> ''

    ;

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 26, 2010 at 8:01 pm

    #1212684

    Thank you both very much.

    I ended up using DTSBackup.

    I'm also saving Jason's code as that looks like it might come in handy.

    Again, thanks so much for your help. Have a good one!

  • August 26, 2010 at 8:02 pm

    #1212686

    You are welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 26, 2010 at 8:08 pm

    #1212687

    Glad it worked out.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply