May 31, 2017 at 11:30 am
Hi All
I want to change the file location of a few databases that are currently located on the wrong the drive.
The databases are log shipped to a secondary server
If i pause the log backup jobs
Detach the databases move the files to a new location reattach the databases in the new location and then restart the log backups
Will the detatch and attach break the log shipping ?- i.e. LSN getting out sync etc
My suspicion is not and it'll be ok however where as I would normally just test this I don't have an test environment to do this at moment so I thought I'd ask here
Gethyn Elliswww.gethynellis.com
May 31, 2017 at 12:01 pm
GRE (Gethyn Ellis) - Wednesday, May 31, 2017 11:30 AMHi AllI want to change the file location of a few databases that are currently located on the wrong the drive.
The databases are log shipped to a secondary server
If i pause the log backup jobs
Detach the databases move the files to a new location reattach the databases in the new location and then restart the log backups
Will the detatch and attach break the log shipping ?- i.e. LSN getting out sync etc
My suspicion is not and it'll be ok however where as I would normally just test this I don't have an test environment to do this at moment so I thought I'd ask here
You should be fine as long as you make sure to specify the new location for the log file.
The database will be in the same state as before the detach with the exception of if you had enabled cross-database ownership chaining. That's lost on an attach.
Sue
May 31, 2017 at 12:06 pm
Thanks Sue, that's what I thought but wasn't 100%
Thanks for taking the time to reply
Geth
Gethyn Elliswww.gethynellis.com
May 31, 2017 at 2:32 pm
Are you trying to move the files of the log shipping primary (where the log backups are taken), or the secondary (where the log backups are restored)?
If the primary, then it'll be fine.
If the secondary, then it won't be fine.
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
June 2, 2017 at 3:43 am
Hi Gail
yYes it was the primary databases. I have now moved them to the correct location with no issue on log shipping
Thanks for the help and taking the time to reply
Geth
Gethyn Elliswww.gethynellis.com
June 2, 2017 at 8:55 am
GRE (Gethyn Ellis) - Wednesday, May 31, 2017 11:30 AMHi AllI want to change the file location of a few databases that are currently located on the wrong the drive.
The databases are log shipped to a secondary server
If i pause the log backup jobs
Detach the databases move the files to a new location reattach the databases in the new location and then restart the log backups
Will the detatch and attach break the log shipping ?- i.e. LSN getting out sync etc
My suspicion is not and it'll be ok however where as I would normally just test this I don't have an test environment to do this at moment so I thought I'd ask here
Do not detach the databases, pause the LS jobs.
Use the command below to alter the file paths in the system catalogs.
ALTER DATABASE ... MODIFY FILE ...
Once done, offline the database, copy and paste the physical files to the new locations and online the database.
When you're happy the move is successful, remove the original files.
You may then resume the LS jobs.
See my article at this link for more info
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply