August 26, 2010 at 5:04 pm
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
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.
August 26, 2010 at 5:12 pm
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
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
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
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