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)
5 Comments. Leave new
I would like to do the same in the mirror server. I don’t want to make any changes in the principal server. In the principal server everything looks good, but in the mirror server I’m facing disk space issue so I just want to move few database files from one disk to other disk. How can I achieve this?
ALTER DATABASE should work on Mirror.
1. Pause mirroring on the principle server.
2. On the DR server run the alter database statements:
ALTER DATABASE MODIFY FILE(NAME = [logical filename], FILENAME = N'[path]+[physical filename]’);
3. Stop the SQL Server service on the DR (using the SQL Server configuration manager).
4. Copy the ldf and/or mdf files to the new location.
5. Start the SQL Server service On DR server.
6. Resume mirroring from principle server.
Thanks Pinal! Great Post!
Hi Pinal, Seems you have given the above solution for a mirroring environment which had Witness Server. This is a best solution for Very Larg Database becuase in failover and failback database takes considerable amount time for recovery, some times it is more than our database files move (copy).
Getting downtime for principal and mirror server may difficult at a time in some of the business environments, if this fine then its a suitable solution for our database file move activity.