Moving Database files of Primary AG server to another disk

This task is majorly carried out to move the database if the disk on the server cannot be expanded and has reached its threshold. There are disks that can not be expanded above 2TB depending on the format.

We can follow the below steps to perform the database movement.

To be done on the Primary server,

Just to note the database file information use the below command of dmv sys.sysaltfiles.

sp_helpdb PRODDBname
--PRODDBname 1 E:\MSSQL\Data\PRODDBname.mdf
--PRODDBname_log 2 L:\MSSQL\Log\PRODDBname.ldf

Replace the database logical file name and the new folder path to move the database.

ALTER DATABASE PRODDBname MODIFY FILE (name='PRODDBname',FILENAME = N'D:\MSSQL\Data\PRODDBname.mdf')

Remove the database from AG group using the below command and take the database Offline.

ALTER AVAILABILITY GROUP AG_async REMOVE DATABASE PRODDBname;

ALTER DATABASE PRODDBname SET OFFLINE WITH ROLLBACK IMMEDIATE

Copy file to another disk with large free space

Once the copy is completed bring the database Online.

ALTER DATABASE PRODDBname SET ONLINE

Now, let us add the database back to the existing AG group.

ALTER AVAILABILITY GROUP AG_async ADD DATABASE PRODDBname;

As the Secondary sync will be lost we need to add the secondary to the AG again

ALTER DATABASE PRODDBname SET HADR AVAILABILITY GROUP = AG_async;

This has been tried and tested. Let me know if it helps!!