Back up SSIS Catalog Database on Azure

Kim, Yong 1 Reputation point
2022-02-10T21:36:07.547+00:00

I am looking for a way to back up SSIS Catalog Database that was deployed on Azure. I looked through the documentation here: https://learn.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver15

It seems like the first step of doing that would be backing up a master key, which is not supported on Azure, so I tried to look for more general way of backing up SQL server on Azure like this method of using Azure Portal GUI: https://cloud.netapp.com/blog/hosted-database-backup-azure

or using SSMS export wizard: https://learn.microsoft.com/en-us/sql/relational-databases/data-tier-applications/export-a-data-tier-application?view=sql-server-ver15

However, it seems like they all fail to back up SSIS Catalog DB, giving this Error SQL71564:

One or more unsupported elements were found in the schema used as part of a data package.

Error SQL71564: Error validating element Signature for '[internal].[get_database_principals]': The element Signature for '[internal].[get_database_principals]' cannot be deployed. This element contains state that cannot be recreated in the target database.

Error SQL71564: Error validating element Signature for '[internal].[get_principal_id_by_sid]': The element Signature for '[internal].[get_principal_id_by_sid]' can...

I am investigating the details of these error messages, and they seem to mean that I should change the metadata or structure of the SSISDB, which I can't really do at the moment. Is there anyway to backup SSIS DB on Azure without really changing SSIS DB?

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,460 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 33,296 Reputation points
    2022-02-11T03:05:16.757+00:00

    Hi @Kim, Yong ，

    Besides SSMS export wizard, you may also use SSIS Tools and Export a Data-tier Application using SSMS to meet your need.

    Check this link which provides detail steps to see if it could resolve the issue.

    how-to-backup-azure-sql-database-to-local-machine

    Regards，

    Zoe

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.