How to Move mirrored database files

The following article describes how to move the database files of a mirrored database to a new location without any downtime. Basically I have the following scenario:

Principal Server N1SQL1VS1 Dbname is Deepak
Mirror Server N1SQL1DEV1MAAKATMAI
Witness Server N1SQL1VS1SQL2008 – Dbname is Deepak

move_mirrored_database_1

I have used witness server to facilitate automatic failover. All the 3 machines are having SQL 2008 RTM Enterprise edition as shown in the below screenshot.

I am moving the log file from C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA folder to C:\Temp folder.

move_mirrored_database_2

The steps I am mentioning below are applicable for SQL 2005 and SQL 2008 servers.

Step1:

Execute the below query in Principal server,

USE Master
GO
ALTER DATABASE Deepak
MODIFY FILE (NAME='Deepak_log', FILENAME='C:\TEMP\Deepak_log.ldf')
GO

The following will be the output of the above command.

The file “Deepak_log” has been modified in the system catalog. The new path will be used the next time the database is started.

Step2:

Now stop SQL Server in principal server N1SQL1VS1, move the deepak_log.ldf file to C:temp in principal server. As shown in the below screenshot the mirror server N1SQL1DEV1MAAKATMAI now becomes the new principal server and will be in disconnected state as the SQL Server in original principal server is down.

move_mirrored_database_3

Step3:

Start SQL Service in N1SQL1VS1 and it will now become the new mirror server and the database will be in Restoring state. The new principal server is N1SQL1DEV1MAAKATMAI and will be in Synchronized state as shown in the below screenshot.

move_mirrored_database_4

Step4:

Now to move the log file in the new principal server N1SQL1DEV1MAAKATMAI we have to repeat the steps mentioned in Step1. The database files reside in D:\databasedata folder and will be moved to C:\temp folder.

Step5:

Execute the below query in new Principal server, N1SQL1DEV1MAAKATMAI

USE Master
GO
ALTER DATABASE Deepak
MODIFY FILE (NAME='Deepak_log', FILENAME='C:\TEMP\Deepak_log.ldf')
GO

The following will be the output of the above command.

The file “Deepak_log” has been modified in the system catalog. The new path will be used the next time the database is started.

Step6:

Now stop SQL Service in the new principal server N1SQL1DEV1MAAKATMAI (failover will happen and the now the principal server is N1SQL1VS1 and mirror server will be N1SQL1DEV1MAAKatmai) and move the database files to the new location C:Temp and start SQL Server as shown in the below screenshot.

move_mirrored_database_5

Step7:

Connect to the new principal server N1SQL1VS1 and run sp_helpdb deepak and check the location of the database files. As shown in the below screenshot it will now reside in the new location C:temp.

Similarly do a failover and run sp_helpdb deepak, you can see that the new location for the new database as C:Temp.

move_mirrored_database_6


Posted

in

by

Comments

3 responses to “How to Move mirrored database files”

  1. SathiReddy avatar
    SathiReddy

    Good article. Helped me in learning new things

  2. VINCENT ANONG avatar
    VINCENT ANONG

    I am very grateful with the explanations on Database mirroring, and Log shipping.
    How can I have full lecture notes and explanations on High Availability and Disaster Recovery.

    1. VidhyaSagar avatar

      Thanks Vincent for going through our articles. I would say there are lot of things changed and lot of new features, we haven’t covered all possible features.

Leave a Reply

Your email address will not be published. Required fields are marked *