Introduction
The SSISDB database (a.k.a. the SQL Server Integration Services catalog) was introduced back in SQL Server 2012 as a way to de-clutter the MSDB database and provide some built-in logging and reporting infrastructure. In a nutshell, SSISDB is an SSIS framework making SQL Server Integration Services more robust and enterprise-friendly by providing the following features:
- Database backup
- Database encryption
- Support for environments
- Support project and package parameters
- Package versioning
- Custom SSRS performance reports built into SSMS
- Deployment directly from SSDT
Fortunately or unfortunately, installation of a new SQL Server instance does not include the creation of the SSISDB Catalog, even if SQL Server Integration Services is included in the installation. For some reason, Microsoft decided to leave that part to the user (luckily, this is a one-time sunk cost).
Once SQL Server installation is completed, you would need to create Catalog using SSMS (see Fig #1) by expanding Management, right clicking on Integration Services Catalogs, and clicking on Create Catalog menu item (see Figure 1).
Problem
Once you are done with the full SQL Server installation and ready to create Catalog (SSISDB Catalog to be exact), you might be “greeted” with the following error – “The catalog backup file 'C:\Program Files\Microsoft SQL Server\140\DTS\Binn\SSISDBBackup.bak' could not be accessed. Make sure the database file exists, and the SQL Server service account is able to access it.(Microsoft.SqlServer.IntegrationServices.Common.ObjectModel)“.
I’m sure you were surprised as I am! What? Why would creation of SSISDB Catalog have anything to do with SSISDB database backup (see Fig #2)? Apparently it’s one of the internal steps that Microsoft does behind the scenes when you are installing SQL Server. To be absolutely specific, installation of an Integration Services feature. Strange, right? The real problem is that when you are trying to create an SSISDB Catalog, Microsoft “assumes” that there is database backup file (see Figure 2).
Solution
Apparently, if you forget to enable installation of Integration Services feature, this is the message that you are going to see. The solution involves going back to the installation (see Figure 3) and checking the Integration Services feature.
Once you have Integration Services enabled (see Figure 4) and the installation completed, you should be able to Create Catalog without any problem.
Disclaimer
This blog post is based on the following forum discussion https://www.sqlservercentral.com/forums/topic/missing-ssisdbbackup-bak.
