How to move database files of a Mirrored SQL Server Database

By:   |   Comments (14)   |   Related: 1 | 2 | 3 | > Database Mirroring


Problem

As you may know, you cannot detach a mirrored database or bring it offline to move a data or log file from one drive to another drive. Moving a database file for a mirrored database is not the same as moving a normal database. Here I will show you the step by step process on how to move the data and/or log file(s) from one drive to another drive with minimum downtime.

Solution

Moving database files can be done two ways; by detaching the database then moving the database file(s) to the target location and then attaching the database from the new location. The other option is to run an ALTER statement to change the file location in the system catalog view, bring the database offline, then copy the file(s) to the target location and bring the database online. With database mirroring enabled for the database, both options will fail because your database is mirrored. We can't detach the mirrored database, nor can we bring it OFFLINE.

Here is step by step solution to reduce your downtime and move your database file from one location to another location for a mirrored database.

Steps

Step 1
Check the database files location for all database files.  Here we are going to move database "NASSP2".

sp_helpdb NASSP2

Checking current database files location

Here we can see two database files placed on the C: drive. As per best practice, we should not place database files on the system C: drive, so this is what we are going to move.

Step 2
Check the database mirroring configuration for your database. Run the below script to check the database mirroring status and its current partner name.

SELECT (SELECT DB_NAME(7)AS DBName),
database_id,
mirroring_state_desc,
mirroring_role_desc,
mirroring_partner_name,
mirroring_partner_instance
FROM sys.database_mirroring
WHERE database_id=7

Checking Mirror DB role and Its partner Name

We can see the mirroring_role_desc for this server is principal and its partner/mirrored instance name.

Step 3
If the database file size is big it will take some time to copy from one drive to another drive. So to over come this issue and minimize the downtime, we will failover our database from our principal server to its MIRROR server and route our application to the new principal server (earlier mirrored box) to bring the application online and run business as usual. Run the below command to failover this database. 

ALTER DATABASE NASSP2 SET PARTNER FAILOVER

Failover principle database to its mirrored server before moving files

Step 4
Now we can again check the database mirroring configuration to see the current mirroring state. Run the same script which we ran in step 2. This time the output is the same, except one column. Here mirroring_state_desc is MIRROR where earlier it was principal.

Checking mirroring state after failover

Now our principal instance has become mirrored. Ask your application team to change the ODBC configurations and route the application connection to the new principal server. Now we can do make changes without downtime being of any concern. Note that if you have databases in a shared environment, then you may need to failover all databases to the mirrored server to reduce any downtime.  The technique requires stopping the database services, so this could impact other databases on this server.

Step 5
As we saw in step 1, two database files are on the C: drive. Now we have to move these two database files from 'C:' to 'E\MSSQL2008\DATA' drive. First we need to run an ALTER DATABASE statement to change the file location in master database system catalog view. Make sure to run this ALTER statement for every file that needs to be moved to the new location.

ALTER DATABASE NASSP2
MODIFY FILE (NAME='NASSP2_System_Data', FILENAME='E:\MSSQL2008\DATA\nassp2_system_data.mdf')
go
ALTER DATABASE NASSP2
MODIFY FILE (NAME='NASSP2_log', FILENAME='E:\MSSQL2008\DATA\nassp2_log.ldf')
go

Change file location in master database system catalog view

Step 6
Now, stop the SQL Server instance to copy the data and log file(s) to the target location. I used PowerShell to stop and start the services. You can use services.msc utility or SQL Server Configuration Manager as well.

STOP-SERVICE MSSQLSERVER -FORCE

STOP SQL Server Instance

Check the status of SQL Server service.

GET-SERVICE MSSQLSERVER

Check SQL Server Status after Instance Down

Step 7
Now copy both database files (nassp2_system_data.mdf and nassp2_log.ldf) to the new target location ('C' to 'E:\MSSQL2008\DATA').

Step 8
Once both files has been copied, start the SQL Server services.

START-SERVICE MSSQLSERVER

START SQL Server Instance

Check the status of SQL Server service

GET-SERVICE MSSQLSERVER

Check Status After SQL Servr Start

Step 9
Once SQL Server has started, failback your database from current principal to your primary box. Run step 1 again to check the location of the files and run step 2 again to check the mirroring status.

Check db file location after file location
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, April 14, 2016 - 2:08:53 AM - Anoop Agarwal Back To Top (41215)

 Hi, Just one question about this move tip. Once we have moved files and switched roles back to orignal ones. As mirroring works on physical level, will it not try to move files on the mirror as well.

 

Wednesday, September 24, 2014 - 2:30:09 AM - Ashish Back To Top (34683)

DATABASESNAPSHOT not working in sql server 2008 R2

Error Msg.

Msg 1844, Level 16, State 1, Line 1

Database Snapshot is not supported on Standard Edition (64-bit).

How to enable it.?

 

Wednesday, September 10, 2014 - 12:31:28 PM - BDS Back To Top (34476)

Does this work on SQL 2012 SP2?  When I try to do the modify file command on the mirror, I get the error below.  Seems like the instance no longer needs to restart in SQL 2012 for this to be updated in the catalog...but I am no DBA...

 

Directory lookup for the file "E:\someapp.mdf" failed with the operating system error 3(The system cannot find the path specified.).

 

Msg 954, Level 14, State 1, Line 1

 

The database "someapp" cannot be opened. It is acting as a mirror database.

 

 

Thursday, May 1, 2014 - 12:57:28 PM - Swami Back To Top (30586)

Hi Neil Miller,

 

Very good suggestion.

 

As you said take the database offline, But when I treid to take offline the database, It was showing the error that "database cannot be taken offline, as it is in database mirroring."

So I copied the data file to new location directly and refreshed the database. but it was showing (in recovery), later it came to synchronized state. Now everything is working fine.

But I want to know, how did you took the database offline.

 

 

Tuesday, August 20, 2013 - 12:55:02 PM - Neil Miller Back To Top (26392)

I have found by tweaking what has been suggested above, that you can do this without having to stop and start SQL Server. i.e. allowing you to keep all other databases online.

To do this you run the ALTER DATABASE statement on the mirror instance to move the files to the new locations.

Then you failover the mirror. At this point it will not be possible to open the database because it cannot see its new files.

Then take it offline, move the files to the new location, and bring it online. It will come back as the primary, with all mirroring working OK.

Then fail it back.

Repeat for the other node.

Thursday, June 6, 2013 - 12:42:26 AM - BalaKrishna Back To Top (25317)

Manvendra

GOOD ONE

Wednesday, July 11, 2012 - 5:28:10 PM - Jason Adams Back To Top (18452)

Found this article while coming up with a plan to relocate files for one of our mirrored databases.  In our situation, I had the following conditions/constraints:

  • Multiple databases on our primary instance
  • Cannot take the primary SQL Server instance down
  • Can only impact the availability of the specific database for which files need to be relocated
  • Do not want to go through process of restoring the database to the mirror instance from last full backup and subsequent diff/log backups to re-establish mirroring.

Here is the process I used.

  1. On the SQL Server instance hosting the mirror Use ALTER DATABASE MODIFY FILE to update the file.
  2. Take the SQL Server instance hosting the mirror down by stopping services (or cluster resource if a clustered instance).
  3. Move the file which needs to be relocated.
  4. Bring SQL Server services for mirror instance back online.
  5. Verify mirroring of the database is resumed, and that principal and mirror are synchronized
  6. On the SQL Server instance hosting the principal, use ALTER DATABASE [database_name] SET PARTNER OFF to stop mirroring of the databases.
  7. On the SQL Server instance hosting the principal, use ALTER DATABASE MODIFY FILE to update file location.
  8. Set the database on the principal instance offline.
  9. Move the file to the new location.
  10. Set the database on the principal instance oneline.
  11. Issue ALTER DATABASE [database_name] SET PARTNER = statements on mirror and principal instances to re-establish mirroring for the database.

If there are no log backups of the database on the principal instances between the completion of steps 5 through 11, mirroring will re-establish without issue.  If log backups did occur while those steps were being performed, restore those log backups to the database on the mirror instance and then issue ALTER DATABASE SET PARTNER = statements to establish mirroring.  This can be avoided if you can afford to stop any scheduled log backups while performing the above operations.

Tuesday, June 19, 2012 - 10:29:18 AM - Manvendra Back To Top (18101)

Hi Doug,

 

Mirror should not be break in SQL Server 2008R2 as well bec I have successfully tested it in SQL Server 2008R2 version as well. Not sure why your mirror break. perform this activity step by step again in test box before doing into PROD.

 

Leme know If you face any issue again.

 

Regards

Manvendra

Tuesday, June 19, 2012 - 8:43:55 AM - Doug Back To Top (18100)

Hey guys Dont try this on SQL 2008R2,

It causes the mirrors to break :(

Just a heads up

 

Doug

Wednesday, May 30, 2012 - 12:43:52 PM - Shamas saeed Back To Top (17730)

I have not verified this thing but it is good information if we have requirement to move db.

Wednesday, May 30, 2012 - 12:22:42 PM - Manvendra Back To Top (17729)

You cant detach any db which is in database mirroring configuration whether its in paused status or syncronized status. You have to drop db mirroing configuration to detach any db.

 

Also we are restarting our secondary Instances so there will not be any issue in terms of downtime.

 

Regards

Manvendra

Wednesday, May 30, 2012 - 10:41:50 AM - Vamsi Back To Top (17726)

Hi,

After failover to secondary, cant you pause the mirroring and detach the database, move the files and attach the database? That way you dont have to restart the database server.

Vamsi.

Wednesday, May 30, 2012 - 7:42:20 AM - Devi Prasad Back To Top (17722)

Nice Article


Wednesday, May 30, 2012 - 4:09:09 AM - deepak Back To Top (17716)

Very Good Article. This will really reduce downtime for Large Database. Keep it up. We are waiting such a brilliant article in near future.

 

Thanks

Deepak

 














get free sql tips
agree to terms