Restore SSIS without backup and only from MDF and LDF files

Question

Restore SSIS without backup and only from MDF and LDF files

ocsnew

SSC Enthusiast

Points: 114
More actions
June 6, 2019 at 9:39 am

#3650688
I am needing to restore / rebuild an SSIS Catalog on a new SQL Server. Problem is, I don't have a backup and only have the MDF and LDF file. I am unfamiliar with SSIS so I have tried attaching the DB but it doesn't re-create the catalog and the jobs.
I know the password to the SSISDB. What steps can I do for me to be able to restore? I am thinking of the following but unsure whether it is the way to go:
1. Create a new instance of the SSIS Catalog (thereby creating a new SSISDB mdf and ldf files)
2. Stop all SQL Services
3. Replace the newly created SSISDB mdf and ldf files with the old ones I have
4. Start all SQL Services
I have done the above steps successfully for the msdb system database so I would like to know if it will also work for the SSISDB. If not, what steps do you recommend? Additionally, is there another way for me to re-locate where the SSISDB will be stored aside from the C: drive?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Do you have the Master Key from the original server? That is, the key used to encrypt (some of) the data inside SSISDB?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Note also that SSISDB does not contain any jobs, merely packages, logs, environments and configurations.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ocsnew SSC Enthusiast Points: 114 More actions · Answer 3

Is the master key different from the password? I have the password but I'm not sure if it's the master key.

Yes you're right, I have the SQL jobs already from the MSDB, what I meant was I need to get the packages inside the SSISDB and basically build the SSIS Catalog, since the work of the SQL Jobs are to run the packages inside it.

I only have the MDF and LDF files though.

ocsnew SSC Enthusiast Points: 114 More actions · Answer 4

Nevermind, I was able to solve my problem. However I didn't do it via the MDF and LDF but rather I was able to find a copy of the old project files. I just re-deployed the project to a new SSIS catalog in the new environment