December 20, 2009 at 8:38 pm
Hi there
How to back up all the SSIS packages from MSDB? like for e.g if i need to backup database
i use this script
BACKUP DATABASE abc TO DISK = 'C:\abc.bak' WITH INIT, NAME = 'abc'
BACKUP DATABASE xyz TO DISK = 'C:\xyz.bak' WITH INIT, NAME = 'xyz'
i need similars scripts like this to backup all my SSIS packages
I tried backing up the MSDB but i could not able to find any SSIS package
after restoring it with different name on same instance
Cheers
December 21, 2009 at 5:17 am
Hi,
the packages are stored in the [msdb].[dbo].[sysdtspackages90] table. The source xml of the package is stored in the [packagedata] column as image.
If you want to see the source XML of the package, you can simply convert it by simple select.
SELECT
[name]
,CONVERT(xml, CONVERT(varbinary(max), [packagedata])) as XMLPackageData
,[id]
,[description]
,[createdate]
,[folderid]
,[ownersid]
,[packagedata]
,[packageformat]
,[packagetype]
,[vermajor]
,[verminor]
,[verbuild]
,[vercomments]
,[verid]
,[isencrypted]
,[readrolesid]
,[writerolesid]
FROM [msdb].[dbo].[sysdtspackages90]
If you store the content of the XMLPackageData into .dtsx file, you have the original package.
If you make a backup of your msdb database and restore it under other name, you will not see the packages on the server as the packages are being red from msdb. You can access them by the above mentioned query with appropriate database name
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply