Missing SSISDBBackup.bak

  • jean.mbadi

    SSC Veteran

    Points: 281

    Hello Team,

    My concern is as followed:
    I've built a project with SSDT 2015 then I would like to deploy the package it in SSMS 2016. However, the error message was that there was no SSISDB.
    I went to SSMS2016 to create the catalog (right-clicking - Integration Services Catalog). The error message was:

    TITLE: Microsoft SQL Server Management Studio

    The catalog backup file 'C:\Program Files\Microsoft SQL Server\130\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)

    After a thorough search, some suggestion were to look at that missing file in C:\Program Files\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak.
    That file isn't there and I've got no other server to copy the file.

    Some suggestions were to reinstall SSDT, something I did but still no success to deploy the package. I hope I will get help from you here, please do explain using a step-by-step basis, I definitely would like to complete that lab, your help will be deeply appreciated.

    Best Regards
    J. M.

  • Lowell

    SSC Guru

    Points: 323450

    Just checking the basics, but is there any chance that the server you are connecting to is an Express instance, and not Developer/Standard/Enterprise?
    If it were express, I'd expect a trimmed down version to be in place on disk?

    Also something weird, On Developer 2016,   I do not have that file in the path you gave (Program Files)
    but I do see it in (x86) folder.

    C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak

    maybe you do have the file, but you need to copy it over to teh place the registry expects?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • jean.mbadi

    SSC Veteran

    Points: 281

    Lowell - Monday, May 8, 2017 11:47 AM

    Just checking the basics, but is there any chance that the server you are connecting to is an Express instance, and not Developer/Standard/Enterprise?
    If it were express, I'd expect a trimmed down version to be in place on disk?

    Also something weird, On Developer 2016,   I do not have that file in the path you gave (Program Files)
    but I do see it in (x86) folder.

    C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak

    maybe you do have the file, but you need to copy it over to teh place the registry expects?

    Hello Lowell,
    Thanks for a quick reply. Although I did not mention it, I am using SQL Server developer edition, also the file on x86 does not exist as you can see the picture below.

    Best Regards

  • Phil Parkin

    SSC Guru

    Points: 244562

    I have 2016 Developer installed, and the path to that file for me is 
    C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn
    Please confirm that you have SQL Server 2016 installed (and not just SSMS 2016). The \120 path you are looking at is for SQL Server 2014.

    --Edit: just noticed that there is also a version of the file here:
    C:\Program Files\Microsoft SQL Server\130\DTS\Binn

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • jean.mbadi

    SSC Veteran

    Points: 281

    Phil Parkin - Tuesday, May 9, 2017 5:58 AM

    I have 2016 Developer installed, and the path to that file for me is 
    C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn
    Please confirm that you have SQL Server 2016 installed (and not just SSMS 2016). The \120 path you are looking at is for SQL Server 2014.

    --Edit: just noticed that there is also a version of the file here:
    C:\Program Files\Microsoft SQL Server\130\DTS\Binn

    Hello Phil,
    Indeed, as the picture below, SQL Server 2016 is well installed as I guess SSMW 2016 is just the GUI. As stated previously, this is a fresh installation and that file does not exist in that location. I probably might need someone file, as it goes unless there is another way. Your help is deeply appreciated, please.
    Regards
    J.M

  • Phil Parkin

    SSC Guru

    Points: 244562

    You are not quite correct. SSMS 2016 is just the client part of the install and it can easily be used to access servers with older versions of SQL Server installed.
    Please run the following commands after connecting to the server to double check:
    SELECT
      Version = SERVERPROPERTY('productversion')
    , Level = SERVERPROPERTY('productlevel')
    , Edition = SERVERPROPERTY('edition');

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • jean.mbadi

    SSC Veteran

    Points: 281

    Phil Parkin - Tuesday, May 9, 2017 8:55 AM

    You are not quite correct. SSMS 2016 is just the client part of the install and it can easily be used to access servers with older versions of SQL Server installed.
    Please run the following commands after connecting to the server to double check:
    SELECT
      Version = SERVERPROPERTY('productversion')
    , Level = SERVERPROPERTY('productlevel')
    , Edition = SERVERPROPERTY('edition');

    Hi again,
    Version = 13.0.4001.0
    Level = SP1
    Edition = Developer Edition (64-bit)
    Picture below for confirmation please.

    Regards

  • Phil Parkin

    SSC Guru

    Points: 244562

    OK, good. 
    If you check both
    C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn
    and
    C:\Program Files\Microsoft SQL Server\130\DTS\Binn

    Do the paths exist?
    Do they contain any .bak files at all?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • jean.mbadi

    SSC Veteran

    Points: 281

    Phil Parkin - Tuesday, May 9, 2017 10:39 AM

    OK, good. 
    If you check both
    C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn
    and
    C:\Program Files\Microsoft SQL Server\130\DTS\Binn

    Do the paths exist?
    Do they contain any .bak files at all?

    Hello Phil,
    No .bak files in both locations exist.

    C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn

    C:\Program Files\Microsoft SQL Server\130\DTS\Binn

    Pictures below, please.

    Regards

  • Lowell

    SSC Guru

    Points: 323450

    is there any chance that when you installed Developer2016, you didn't install Integration Services? this screenshot shows the checkbox unchecked; i'd suspect that that might be issue. SSDT is the client side of the operation, but the server side is what installs SSISDB and integration services.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Phil Parkin

    SSC Guru

    Points: 244562

    Lowell - Tuesday, May 9, 2017 11:43 AM

    is there any chance that when you installed Developer2016, you didn't install Integration Services? this screenshot shows the checkbox unchecked; i'd suspect that that might be issue. SSDT is the client side of the operation, but the server side is what installs SSISDB and integration services.

    +1, that was going to be my very next question.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • jean.mbadi

    SSC Veteran

    Points: 281

    Phil Parkin - Tuesday, May 9, 2017 12:20 PM

    Lowell - Tuesday, May 9, 2017 11:43 AM

    is there any chance that when you installed Developer2016, you didn't install Integration Services? this screenshot shows the checkbox unchecked; i'd suspect that that might be issue. SSDT is the client side of the operation, but the server side is what installs SSISDB and integration services.

    +1, that was going to be my very next question.

    Hello Phil,
    I ran the installation again and making sure to select all as pictures show below. What a surprise to find that it's been installed. I want to say big thank you. This process took me more than a week. I will just continue and run Catalogs services.What a file (lol).Thank you, sir.
    Best Regards

  • Phil Parkin

    SSC Guru

    Points: 244562

    Good to hear! Thank you for posting back.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • jean.mbadi

    SSC Veteran

    Points: 281

    Phil Parkin - Tuesday, May 9, 2017 1:43 PM

    Good to hear! Thank you for posting back.

    Thank you very much for a great support, Phil.

    Kind Regards

  • DoctorIce

    SSC Rookie

    Points: 27

    SOLUTION/ANSWER for Missing SSISDBBackup.bak file (SQL Server 2017)

    Gentlemen,

    I suffered from the same problem and found the following fix - for SQL Server 2017 - but approach could work for other platforms.

    This issue seems to happen when you install SQL Server ONLY separately, and then later install SSIS separately.  Somehow the SSISDBBackup file in not created when this sequence of activities is followed.

    Run the SQL Server Installer again and choose the option to modify or add components to existing installation.

    Then click on the option for Integration Services as shown in the image below:

    This causes SQL Server to create a service for SSIS.    Once the process is complete, try re-creating the SSIS Catalog and this should work

    I hope this helps someone out there !

    Keep Rolling Fellas !

     

    • This reply was modified 11 months, 1 week ago by  DoctorIce.

    Doctor Ice, Senior Database Engineer

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply