SQL Server 2014 - Always on availability - Move MDF files
The SQL Servers with always on availability allows you to have fail over options for databases. In an environment where you have always on availability, it requires that we remove the DB from the always on availability before we move the MDF files from one drive to another.
In this post I am going to write about how to move the MDF files where always on availability is enabled and at the next half I will explain how to add the same DB in always on availability. So, before we start, it is expected that you have the knowledge of primary and secondary SQL server.
Move MDF Files
- Login to the primary SQL server, expand Always On Availability
- Select Availability Databases > Press F7
- The object explorer window displays all DBs
- Select the DB/s which you want to remove from Availability group and right click to "Remove Database from Availability Group"
- After the DB is removed from availability, Detach the DB. Right Click on the DB and select Tasks > Detach
- Login to Secondary SQL server
- Remove the DB from the always on availability (refer the steps above)
- Delete the DB/s from the secondary SQL server. Right click on the DB > click Delete > Select close all connections > click Delete
- Copy the MDF files from source to target destination (ex. Disk2 to Disk1)
- Login to Primary SQL Server
- Right click on Databases > Attach > Add > Select the MDF file > click OK
- After the DB is attached we will work to add the DB in always on availability group of the primary and secondary server.
