March 5, 2018 at 3:16 am
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
March 5, 2018 at 4:38 am
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
March 5, 2018 at 5:01 am
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 🙂
March 9, 2018 at 1:37 am
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
June 22, 2021 at 6:51 pm
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.
October 11, 2022 at 7:07 pm
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?
October 11, 2022 at 7:14 pm
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
October 12, 2022 at 4:03 am
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?
October 12, 2022 at 9:37 am
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
October 12, 2022 at 9:50 am
Hi Nic,
Thanks much.
October 12, 2022 at 9:53 am
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