I remember seeing a question on the #sqlhelp hashtag on how to move the secondary database files to a new physical location. While this might seem a mundane task but can throw up a few surprises. If you don’t want to tear down your log shipping configuration and re-establish it with the files in the new physical location, then this post will definitely interest you.

If you are running your log shipping in standby mode, you will first need to switch to norecovery mode. This is due to the fact that you cannot execute an ALTER DATABASE command on a standby database as it is not writable. You will be presented with a following error if you attempt to do so:

Msg 5004, Level 16, State 4, Line 1
To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed.

So to avoid the above issue, you need to switch to norecovery mode. If you are already running in this mode, then you have one task reduced from your list. Keep in mind that the switch of the operating mode takes effect only after the next log restore operation.

Now here are the exact set of steps that you need to follow:

1. Disable the Log Shipping Restore SQL Agent job.
2. Use ALTER DATABASE command to change the location of the secondary database files
3. Stop the SQL Server instance hosting the secondary database files
4. Move the secondary database files to the new location as updated in Step #1. (OS File Copy)
4. Start the SQL Server instance
5. Enable the Log Shipping Restore SQL Agent job

Verify that the log shipping jobs are running without any errors. The reason you need to stop the SQL Server instance is because an ALTER DATBASE…OFFLINE doesn’t work when the database is in RESTORING state. You will be blocked by the following error if you attempt to set the database offline:

Msg 5052, Level 16, State 1, Line 1
ALTER DATABASE is not permitted while a database is in the Restoring state.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Hope the above helps in moving the database files of the secondary databases which are configured for log shipping. The steps mentioned above works for both data and log files.