Moving SSISDB off C drive

  • Hi all,
    I just wanted to get some thoughts on a plan I have to move SSISDB off the C drive and onto another drive on the same machine.

    After doing some reading I came across a MS article on how to back and restore SSISDB but when it comes to moving it they say to move it in the same manner as a user DB.

    So based on that my plan is to do the following;

    1. Wait for the daily backups to run
    2. Stop the SSIS Service
    3. Detach the SSISDB database and copy the files to their new location
    4. Reattach the SSISDB database
    5. Start the SSIS Service again

    I'm going to give this a try on one of our DEV boxes but I just wanted to get your opinion on the plan above and any pitfalls others may have faced.

    Any help would be appreciated.

    Thanks,

    Nic

  • ALTER DATABASE ... MODIFY FILE for both the mdf and the ldf to point then at the new directory.
    Then take the DB offline  if you can (don't know whether SSISDB can be), if not suut SQL down.
    Move the files to their new location
    Put DB online/start up SQL.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hello, you also have the option to backup the DB (or use an existing latest backup) then use RESTORE WITH MOVE TO for each file (mdf, ndf, and ldf).

    This has the benefit of being harder to screw up with typos and its a single command. If there are typos, the RESTORE command would fail ahead than later and wont leave you with 'suspect' or 'in recovery' databases. It also tests your backup 🙂

  • Thank you both for the answers.

    Regarding restoring the database and using MOVE, would this then require to me to restore the master key afterwards? or would that only need to occur if we moved server?

    Thanks,

    Nic

  • database master passwords only come into play when moving to new server.  For example if moving the SSIDB from C: to another drive on same server, PW not required for this operation.

  • Can someone tell- Are we sure to move SSISDB database onto another drive like we do for user databases?

    Is there any precautionary step that we need to take care while moving SSISDB data and log file to another drive?

  • Hi,

    I did this the other day.

    I used this as a guide, worked a treat for me.

    https://blog.pythian.com/how-to-restore-ssisdb-to-another-server-and-migrate-the-ssis-catalog/

    I did create the catalog on my new server before and then just restored over it. My server was a rebuild that included master and msdb so I didnt nee to restore the key (thought I would but not needed).

    Any questions then let me know.

    Thanks,

    Nic

  • Hi,

    Thanks for the reply.

    I need to move database and log file of SSISDB from C: drive to some other drive on the same server.

    I am not restoring it on another server.

    So please suggest if I can move . Mdf and. Ldf files of SSISDB database like we do for user databases?

  • Hi,

    This should do it

    1. Backup the SSIS DB and Key (you can see how to do a key backup on the previous link) - This is just in case something goes wrong, I always like to have at least one alternative.

    2. Stop the SSIS Service

    3. Detach the SSISDB database and copy the files to their new location

    4. Reattach the SSISDB database

    5. Start the SSIS Service again

    Other option would be to use the backup taken in step 1 to restore it to the new location as its the same server then it would just be a normal backup and restore.

     

    hopefully that makes sense, if not then let me know.

    Thanks,

    Nic

  • Hi Nic,

    Thanks much.

     

  • Pleasure, anything else then feel free to post on the forum, someone will be able to answer.

Viewing 11 posts - 1 through 10 (of 10 total)

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