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
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.
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.
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.
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.
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.
Leave a Reply to VidhyaSagar Cancel reply