One Reason to Avoid SSISDB Backup and Restore

First, please do not misunderstand. You should back up SSISDB just like you back up all other databases – especially in Production. You should also conduct Disaster Recovery exercises in which you restore SSISDB from the latest backup, or avail yourself of Always On availability groups and / or Windows Server Failover Clustering.

To demonstrate why I avoid restoring SSISDB to other instances – especially as a mechanism for promoting SSIS in the enterprise:

I created a fresh instance of SQL Server on a virtual machine.
I created an SSIS Catalog on the fresh instance of SQL Server.
I restored a backup of SSISDB from a different instance of SQL Server.
I opened the SSIS Catalog All Executions report and viewed the SSIS Catalog All Executions report:

What’s wrong with this picture?

No SSIS executions have taken place in this SSIS Catalog. Not one. Yet the SSIS Catalog All Executions report displays a list of executions as if these executions had taken place in this SSIS Catalog on this instance of SQL Server.

This report is misleading.

You Can Handle the Truth

SSIS Catalog Compare supports SSIS DevOps by promoting only the SSIS – and not the logs – contained in an SSIS Catalog. To promote an SSIS Catalog Folder and all related contents (even SSIS Catalog Environments that reside in other SSIS Catalog Folders), simply right-click a folder and then click “Deploy Folder and Contents…”:

Once promoted, compare the code in the SSIS Catalogs to view any differences:

In the image above, the Demo folders match (indicated by no text decoration or background color).

Conclusion

Why use SSIS Catalog Compare?

Confidence.

Promote SSIS folders, projects, environments, configuration literals, and references between lifecycle management tiers with confidence.
Migrate on-premises SSIS folders, projects, environments, configuration literals, and references between lifecycle management tiers to the cloud with confidence.