I was responding to a question on MSDN forums and part of the question was about how to backup the SSIS packages after they are deployed onto SQL Server. This is not related to SQL 2012 where SSIS packages can be deployed into it’s own SSISDB database as project model. This question was related to SQL Server 2008R2.
When SSIS packages are deployed, they are stored in MSDB database. So, MSDB database has to be restored if you need to recover the ssis packages. Of course, you should also have to put the ssis packages in a source control and have backups of it.
But an interesting point was raised – “is it possible to restore a single SSIS package”. The concerns mentioned were having to do full restore of msdb database.
I thought this is a real concern because having to restore msdb to recover a ssis package could lead loss of other important information such as backup\restore history etc. So, the question was – Is it possible to recover a single SSIS package without having to restore complete MSDB database?
So, to test this, I created a simple ssis package and deployed to sql server.
The below query returns the ssis packages deployed on the server.
Select * from msdb.dbo.sysssispackages
Now, I copied the data from msdb.dbo.sysssispackages into another table
Select * into newssispackages from msdb.dbo.sysssispackages
Now, I made another change to the SSIS package and redeployed. So, to recover the prior version of ssis package, first,I deleted the SSIS package from table
Delete from msdb.dbo.sysssispackages where name ='TestJob'
and then inserted the corresponding row from another table into dbo.sysssispackages.
Insert into dbo.sysssispackages
Select * from newssispackages where name ='TestJob'
This restored the prior version of SSIS into msdb. So, we can take table backup(Select * into) and revert back to previous versions as explained above.
I am not sure whether this works in every scenario and would be curious to know on scenarios where this won’t work.