SQL SERVER – Move Database Files for a Mirrored Database Without Breaking Mirroring

Database mirroring is a very popular solution for high availability. Later on I am finding quite a many people DBAs opting for mirrored databases.

As faster and faster drives are now available in the market and DBAs are moving their database to faster drives. Recently, I was approached by my older customer who wanted to move their database files to a new drive and they wanted to hire me to do so. I politely pointed them out to my previous article here, which describes all the steps. My goal was simple, if my client can do something on their own, it is better for them as they will learn how to do it as well as save some money.

However, my client insisted that he wanted me to move their database as they had mirrored databases and he was worried if he does all the steps, his mirroring will be disconnected and he will never get that back up. I totally understand his situation, I decided to help him out with the proper steps. I also realized quite a many people also would love to know how to move database files for a mirrored database, hence this blog post.

Let us see in few simple steps, how we can move database file for a mirrored database without breaking or removing mirroring.

All the following steps are to be performed on the principal server and not on mirrored server.

Step 1: Find the Logical and Physical File Details

 USE AdventureWorks2014 -- replace it with your database name
GO
SELECT name AS logical_name, physical_name
FROM sys.database_files
GO

Step 2: Move the Database Files to the new folder

-- Data Files
ALTER DATABASE AdventureWorks2014
MODIFY FILE (NAME = AdventureWorks2014_Data,
FILENAME = 'd:\data\AdventureWorks2014_Data.mdf');
-- Log Files
ALTER DATABASE AdventureWorks2014
MODIFY FILE (NAME = AdventureWorks2014_Log,
FILENAME = 'd:\data\AdventureWorks2014_Log.ldf')

https://blog.sqlauthority.com/i/d/dbmirrorfile2.jpg

Step 3: Stop the SQL Server service on Mirrored Database Instance.

This step is a very important step and do not miss this critical step on the mirrored database.

Step 4: Stop the SQL Server services on the Primary Instance.

You can either stop the SQL Server services from management studio, configuration manager or with command prompt. I prefer to do this via configuration manager.

Step 5: Move the data and log files on the principal server.

Now is the easy part. Go to Windows Explorer and move all the data and log files for the database which you wanted to move.

Step 6: Restart SQL Server services on Primary Instance.

You may see your database in the brief period in the state of “In Recovery” and it will be back online very soon. If you see your database in the state of “Pending Recovery”, you should immediately reach out to SQL Server expert as there may be multiple reasons for this and expert may be able to help you out in this situation.

Step 7: Restart SQL Server services on mirrored Instance.

After a few moments, you should see your primary database mirrored and synchronized.

In the final step, you run the same script which you ran in the step 1 and validate that your database is indeed moved to a new location.

Note 1: This is the easiest method to move your database but it involves a brief downtime, so please try this during your maintenance window.

Note 2: Please make sure that you try this out your development server, before you try out on your production system.

Finally, if you need any help, just reach out to me and I will be available to help you out.

Reference: Pinal Dave (https://blog.sqlauthority.com)