How to move mdf file to other disk drive when it is "Standby / Read-Only" mode?

Question

I am trying to change the location of mdf and ldf file from one disk drive to other disk drive due to storage limit.

Currently, the database that I am trying to move to other drive is in "Standby / Read-Only" mode.

But, my understanding is that, in order move mdf file and attach database, I need to change the mode to "regular" before moving.

Also, my understanding is that if I change the mode from "Standby / Read-Only" mode to something else, I cannot go back to restoring without restoring the backups again.

How do I go about doing it (attaching the "Standby / Read-Only" database after detaching)?

Answer 1

With the database in RESTORING state, run ALTER DATABASE ... MODIFY FILE to change where SQL Server will expect to find the database files. Then shut down SQL Server and move the files there. Shutting down SQL Server is an alternative to setting the database offline or detaching it as outlined in the docs Move User Databases, which can't be done in this case.

See eg: Steps to Move SQL Server Log Shipping Secondary Database Files