About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not a lab validation of the procedures. Check the stated versions and your own recovery requirements before making changes.
SQL Server Integration Services (SSIS) packages often carry a business's quiet but essential work: nightly loads, file imports, data exports to partners. When they are deployed to the Integration Services catalogue, they live in a database called SSISDB, alongside their settings and history.
Because SSISDB is a database, it is easy to assume a normal database restore brings it back on a new server. Microsoft's documentation shows otherwise. A restore to a different instance needs a key that is not usable from the backup alone, and several objects that live outside SSISDB.
This article sets out what Microsoft documents and turns it into a recovery kit.
What the catalogue holds
Microsoft's SSIS Catalog article describes SSISDB as the place that stores the packages that you've deployed to the Integration Services server. It also holds folders, projects, parameters, server environments and their variables, and operational data such as execution and validation history.
Some of that content is sensitive. The page explains that the catalog automatically encrypts the package data and sensitive values when a project is deployed, and decrypts them when they are retrieved. The encryption algorithm is a catalogue property, with AES_256 as the default. Environment variables can also be marked sensitive.
The master key: the part that does not travel by itself
When the catalogue is created, Microsoft's steps ask for a password. The page explains that The password protects the database master key that is used for encrypting the catalog data. It continues: Save the password in a secure location. Also back up the database master key.
Why a new server cannot simply open it
Microsoft's page on SQL Server and database encryption keys explains the hierarchy. Each instance has a service master key, which SQL Server generates the SMK the first time the instance starts and uses to encrypt linked server passwords, credentials and the database master key in each database. A database's master key can be opened automatically because a copy of it is protected by that instance's service master key.
The same page describes the service master key as protected by the local machine key or by the Windows Data Protection API (DPAPI), which uses a key derived from the SQL Server service account's Windows credentials.
Microsoft’s encryption-key documentation says automatic decryption uses a copy of the database master key encrypted with the service master key, stored in both the database and master. In our reading, this explains why a replacement instance cannot assume it has the old instance’s automatic decryption route. Microsoft's restore steps for SSISDB acknowledge this: they include a note that a warning appears because the database master key hasn't yet been encrypted by the service master key on the restoring instance. In that specific documented restore method, Microsoft says to ignore the warning that the current master key could not be decrypted because FORCE was specified. That narrow instruction is not permission to ignore other key or restore errors.
Two ways back in
Microsoft's restore procedure gives two methods for re-establishing the master key:
- Method 1: restore the master key from a file. Microsoft says to 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. It notes that the SQL Server service account needs permission to read the key file.
- Method 2: open the key with the password set when the catalogue was created, then add encryption by the new instance's service master key. Microsoft says to use this method if you have the original password that was used to create SSISDB.
In our reading, a recovery plan needs at least one of these, and preferably both: the master key backup file with its password, and a password that still opens the catalogue master key. Without either, the catalogue's encrypted values cannot be opened on a new server. The pages we read do not describe a way around that, and we do not suggest one.
What else lives outside SSISDB
The "Back up, Restore, and Move the SSIS Catalog" section of Microsoft's SSIS Catalog article lists further steps. Several exist only when the target instance has never hosted a catalogue, which is exactly the situation after a server is lost and rebuilt.
At backup time, Microsoft's steps are to:
- Back up the SSISDB master key to a file, protected by a password.
- Back up the SSISDB database.
- Generate a script for the login named ##MS_SSISServerCleanupJobLogin##, which SSIS creates to run its maintenance job. The page describes it as only for internal use by SSIS.
- For a target that has never had a catalogue, generate a script for the startup procedure sp_ssis_startup, which lives in master.
- For the same case, generate a script for the SSIS Server Maintenance Job, the SQL Server Agent job that helps clean up operation logs outside the retention window and remove older versions of projects.
At restore time, for a target that has never had a catalogue, Microsoft's steps include enabling common language runtime (CLR) integration, and creating an asymmetric key and a login from it with the UNSAFE assembly permission. Microsoft explains that Integration Services CLR stored procedures require UNSAFE permissions because they need access to restricted resources such as the Win32 API.
After restoring SSISDB, the steps continue: run the scripts saved at backup time, set the startup procedure to run automatically, map the catalogue's internal user to the maintenance login, and restore the master key by one of the two methods above. Microsoft then recommends checking that the catalogue schema and the installed Integration Services binaries are compatible, and confirming the restore by running packages.
In our reading, the practical lesson is that the scripts in steps 3 to 5 should be captured before the loss, while the old server still exists. They cannot be generated from the SSISDB backup afterwards. If they were not kept, the objects have to be recreated another way, which Microsoft's pages we read do not describe.
Jobs, schedules and proxies that run the packages
The catalogue stores packages; it does not run them on a schedule by itself. In many installations, SQL Server Agent jobs start catalogue packages, and those jobs live in msdb, not SSISDB.
Job steps that run packages under a different Windows identity use Agent proxies. Microsoft's article Create a SQL Server Agent proxy explains that Each proxy corresponds to a security credential, and that the credential holds the Windows account's details. The encryption keys page states that the service master key is used to encrypt credentials, and that You can't restore connections secured with the service master key unless you have that key.
In our reading, three things follow for a rebuild on a new instance:
- Agent jobs that run packages must be recovered from msdb or from saved job scripts. Restoring SSISDB alone does not bring them back.
- Credentials used by proxies will need their passwords entered again on the new instance unless the old service master key is also restored, which is a separate, instance-wide decision.
- Environment references, which connect projects to server environments, are inside SSISDB and should come back with it, but the external systems they point to must still accept connections from the new server.
Which backup to restore: project versions and history
SSISDB changes whenever a project is deployed, an environment variable is edited or a package runs, so the choice of backup affects what comes back.
The catalogue keeps earlier project versions. Microsoft explains that By default, each time you redeploy a project, the SSISDB catalog retains the previous version of the project. The SSIS Server Maintenance Job removes old versions beyond a set maximum per project, and removes operations data older than a retention period, according to catalogue properties.
In our reading, two consequences follow. First, a restored catalogue may contain several versions of a project, and the one that was current at the backup time is the one that will run. If a deployment happened after the last backup, it will need redeploying from source. We recommend keeping deployment artefacts, and a note of which version is current, outside the server. Second, execution history in the restored catalogue stops at the backup time. Anything that ran after that, including loads that may need to be rerun, will not appear in it, so the run history of the systems the packages feed is the better guide to what needs repeating.
Availability groups are not a backup
Microsoft documents adding SSISDB to an Always On availability group for high availability. The catalogue page warns that automatic SSISDB failover is unsupported until SSIS Support for Always On is enabled after adding SSISDB to the group. It also notes that Contained availability groups, which were introduced in SQL Server 2022, are not supported yet. It also warns: When a failover occurs, packages that were running do not restart or resume.
In our reading, availability groups help when a server fails, but they replicate every change, including a deleted project or an overwritten environment. Backups, the master key and the external objects are still needed, and the master key in particular matters here as well, because every replica needs to be able to open it.
Versions and compatibility
Microsoft's restore steps end with a schema check between the catalogue and the installed Integration Services binaries. In our reading, this means the new server's SQL Server and Integration Services version is part of the recovery plan. A catalogue restored to a server with different binaries may need upgrading before packages run. The same SSIS Catalog page documents the SSISDB Upgrade Wizard for a database older than the target SQL Server instance, including a database restored from an older version. Follow that version-specific process when applicable; this article does not reproduce it.
A recovery kit for SSISDB
From the points above, these are our recommendations for what should exist outside the server:
- SSISDB backups on a schedule that matches how often projects and environments change.
- The master key backup file, refreshed whenever the key changes, and its password, stored separately.
- A current catalogue master-key password, kept as the second route back in.
- Saved scripts for the maintenance job login, the startup procedure and the maintenance job, generated while the server exists.
- SQL Server Agent jobs that run packages, through msdb backups or saved job scripts.
- A list of proxies and credentials, with where their passwords are held, so they can be recreated.
- The SQL Server and Integration Services version in use, and installation media for it.
- A test restore to an instance that has never had a catalogue, ending with a scheduled package that runs and reaches its data.
Verify a package without repeating a business transaction
Running a restored package may insert records, overwrite exports or send data to a partner. A rehearsal therefore needs test endpoints and an agreed input set before any package is enabled. Preserve its schedule as evidence, but keep automatic execution disabled until its external connections and consequences have been reviewed. Successful catalogue validation does not prove that rerunning the last recorded job is safe.
Compare the restored execution history with the systems that received its output. A job can have committed some external work after the catalogue backup was taken, leaving no corresponding success record in the recovered catalogue. The application owner must decide whether to resume, replay or reconcile that work. Acceptance should cover decryption of sensitive parameters, the intended Agent identity, required drivers and a controlled end-to-end package result. Record the exact SQL Server and Integration Services builds used in that check.
Risks and limits of this article
- Based on Microsoft documentation as read on 11 November 2026, for SQL Server 2016 and later.
- Packages stored in msdb or the file system, rather than the catalogue, are outside this article's scope.
- Statements about why the master key does not open on a new server, and about Agent jobs and credentials, are our reading of Microsoft's pages, labelled as such.
- No restores, key operations or scripts were run for this article, and Microsoft's code is deliberately not reproduced.
Sources
Writer research: 11 November 2026. Independently reopened for review: 13 November 2026.
- Microsoft Learn: SSIS Catalog, including the section "Back up, Restore, and Move the SSIS Catalog"
- Microsoft Learn: SQL Server and database encryption keys
- Microsoft Learn: Create a SQL Server Agent proxy
This article was originally published on .