Moving a mirror database’s physical files

Today a request came in from a client to move the ldf file of a mirror database to a new SAN drive on the DR server. My immediate thought was that I would have to break the mirroring solution, recover the database and then move the files. Luckily I didn’t have to go that far – I was able to alter the database metadata while the database was still in the restoring state. For posterity (or otherwise) here are the steps I followed:

  1.  Ensure that mirroring is fully synchronized.
  2.  Pause mirroring on the primary server.
  3.  On the DR server run the alter database statements:
    ALTER DATABASE MODIFY FILE(NAME = [logical filename], FILENAME = N'[path]+[physical filename]');
  4.  Stop the SQL Server service on the DR (using the SQL Server configuration manager).
  5.  Copy the ldf and/or mdf files to the new location.
  6.  Start the SQL Server service.
  7.  Check the SQL Server error logs and ensure that the restart was error free.
  8.  Resume mirroring. (It may take a few minutes for the connection to re-establish and allow mirroring to be resumed. Try refreshing the database view in the SSMS object explorer until the status shows as suspended)
  9. Monitor mirroring for re-synchronization and clean up the old files.

Easy 🙂

This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s