Move SQL Server Database Files
My current environment is Always On with asynchronous mode with readable secondary's, but no failover. I wanted to move database file which is already in AG. But I can't failover AG. Please advise best possible way .
I had a very lengthy write-up on this. However, it turns out that SQLShack already has a great https://www.sqlshack.com/a-walk-through-of-moving-database-file-in-sql-server-always-on-availability-group/ .
Without going into too much detail, here is a high level summary of what they suggest.
- Connect to your secondary replica.
- Remove the database from the Availaibly Group on the secondary replica. It should now show as Restoring.
- Issue the move files command from the master database.
- Stop the SQL service on the secondary replica.
- move the files to the new location.
- Start the SQL service
- Check the file location in sys.master_files
- Join the database back to the Availability Group.
Repeat for all remaining replicas. For your primary replica, you're going to need to failover.
If that's not an option, scrap the above method all together and do the following...
- Remove the database from the Availability Group
- Issue the move file command
- Set the database to OFFLINE
- Move the data and log files
- Bring the database back ONLINE
- Add the database back to the Availability Group.