Backup, Restore, and Move the SSIS Catalog

SQL Server 2012 Integration Services (SSIS) includes the SSISDB database. You query views in the SSISDB database to inspect objects, settings, and operational data that are stored in the SSISDB catalog. This topic provides instructions for backing up and restoring the database.

The SSISDB catalog stores the packages that you’ve deployed to the Integration Services server. For more information about the catalog, see SSIS Catalog.

To Back up the SSIS Database

1. Open SQL Server Management Studio and connect to an instance of SQL Server.

2. Back up the master key for the SSISDB database, by using the BACKUP MASTER KEY Transact-SQL statement. The key is stored in a file that you specify. Use a password to encrypt the master key in the file.

   For more information about the statement, see BACKUP MASTER KEY (Transact-SQL).

   In the following example, the master key is exported to the c:\temp directory\RCTestInstKey file. The LS2Setup! password is used to encrypt the master key.

   backup master key to file = 'c:\temp\RCTestInstKey'
          encryption by password = 'LS2Setup!'
   

3. Back up the SSISDB database by using the Backup Database dialog box in SQL Server Management Studio. For more information, see How to: Back Up a Database (SQL Server Management Studio).

4. Generate the CREATE LOGIN script for ##MS_SSISServerCleanupJobLogin##, by doing the following. For more information, see CREATE LOGIN (Transact-SQL).

   1. In Object Explorer in SQL Server Management Studio, expand the Security node and then expand the Logins node.

   2. Right-click ##MS_SSISServerCleanupJobLogin##, and then click Script Login as > CREATE To > New Query Editor Window.

5. If you will be restoring the SSISDB database to an SQL Server instance where the SSISDB catalog was never created, generate the CREATE PROCEDURE script for sp_ssis_startup, by doing the following. For more information, see CREATE PROCEDURE (Transact-SQL).

   1. In Object Explorer, expand the Databases node and then expand the master > Programmability > Stored Procedures node.

   2. Right click dbo.sp_ssis_startup, and then click Script Stored Procedure as > CREATE To > New Query Editor Window.

6. Confirm that SQL Server Agent has been started

7. If you will be restoring the SSISDB database to an SQL Server instance where the SSISDB catalog was never created, generate a script for the SSIS Server Maintenance Job by doing the following. The script is created in SQL Server Agent automatically when the SSISDB catalog is created. The job helps clean up cleanup operation logs outside the retention window and remove older versions of projects.

   1. In Object Explorer, expand the SQL Server Agent node and then expand the Jobs node.

   2. Right click SSIS Server Maintenance Job, and then click Script Job as > CREATE To > New Query Editor Window.

To Restore the SSIS Database

1. If you are restoring the SSISDB database to an SQL Server instance where the SSISDB catalog was never created, enable common language runtime (clr) by running the sp_configure stored procedure. For more information, see sp_configure (Transact-SQL) and clr enabled Option.

   use master 
          sp_configure 'clr enabled', 1
          reconfigure
   

2. If you are restoring the SSISDB database to an SQL Server instance where the SSISDB catalog was never created, create the asymmetric key and the login from the asymmetric key, and grant UNSAFE permission to the login.

   Create Asymmetric key MS_SQLEnableSystemAssemblyLoadingKey
          FROM Executable File = 'C:\Program Files\Microsoft SQL Server\110\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll' 
          
   

   Integration Services CLR stored procedures require UNSAFE permissions to be granted to the login because the login requires additional access to restricted resources, such as the Microsoft Win32 API. For more information about the UNSAFE code permission, see Creating an Assembly.

   Create Login MS_SQLEnableSystemAssemblyLoadingUser
          FROM Asymmetric key MS_SQLEnableSystemAssemblyLoadingKey 
    
          Grant unsafe Assembly to MS_SQLEnableSystemAssemblyLoadingUser
   

3. Restore the SSISDB database from the backup by using the Restore Database dialog box in SQL Server Management Studio. For more information, see the following topics.

   • Restore Database (General Page)

   • Restore Database (Files Page)

   • Restore Database (Options Page)

4. Execute the scripts that you created in the Backup the SSISDB Catalog procedure for ##MS_SSISServerCleanupJobLogin##, sp_ssis_startup, and SSIS Server Maintenance Job. Confirm that SQL Server Agent has been started.

5. Run the following statement to set the sp_ssis_startup prodecure for autoexecution. For more information, see sp_procoption (Transact-SQL).

   EXEC sp_procoption N'sp_ssis_startup','startup','on'
   

6. Map the SSISDB user ##MS_SSISServerCleanupJobUser## (SSISDB database) to ##MS_SSISServerCleanupJobLogin##, by using the Login Properties dialog box in SQL Server Management Studio.

7. Restore the master key by using one of the following methods. For more information about encryption, see Encryption Hierarchy.

   • Method 1

     Use this method if you’ve already performed a backup of the database master key, and you have the password used to encrypt the master key.

            Restore master key from file = 'c:\temp\RCTestInstKey'
            Decryption by password = 'LS2Setup!' -- 'Password used to encrypt the master key during SSISDB backup'
            Encryption by password = 'LS3Setup!' -- 'New Password'
            Force
     

     Note

     Confirm that the SQL Server service account has permissions to read the backup key file.

     Note

     You will see the following warning message displayed in SQL Server Management Studio if the database master key has not yet been encrypted by the service master key. Ignore the warning message.

     The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.

     The FORCE argument specifies that the restore process should continue even if the current database master key is not open. For the SSISDB catalog, because the database master key has not been opened on the instance where you are restoring the database, you will see this message.

   • Method 2

     Use this method if you have the original password that was used to create SSISDB.

     open master key decryption by password = 'LS1Setup!' --'Password used when creating SSISDB'
            Alter Master Key Add encryption by Service Master Key
     

8. Determine whether the SSISDB catalog schema and the Integration Services binaries (ISServerExec and SQLCLR assembly) are compatible, by running catalog.check_schema_version.

9. To confirm that the SSISDB database has been restored successfully, perform operations against the SSISDB catalog such as running packages that have been deployed to the Integration Services server. For more information, see Run a Package on the SSIS Server Using SQL Server Management Studio.

To Move the SSIS Database

• Follow the instructions for moving user databases. For more information, see Move User Databases.

  Ensure that you back up the master key for the SSISDB database and protect the backup file. For more information, see To Back up the SSISDB Catalog.

  Ensure that the Integration Services (SSIS) relevant objects are created in the new SQL Server instance where the SSISDB catalog has not yet been created.