Move Data or Transaction File in SQL Server

Sometimes the database data or transaction file are resided in the hard drive which is running out of space. One solution is to move the files to the other drive has enough space. We can move Data or Transaction File in SQL Server by detaching and attaching the database.

For the best practice, we should make sure there is no connection to the database which file we want to move. We can use "Activity Monitor" in SQL Server Management Studio to check all the sessions are connecting to the databases on the SQL server.

If there is no connection to the database that we will manipulate, we detach and attach the database through following steps:

1. In   SQL Server Management Studio, select and right click the database, and then go to "Tasks" -> "Detach" . 
2. After the database is detached successfully, we can move the data or transaction file within file system. 
3. Next, we right click "Databases" and select "Attach ...". 
4. In the "Attach Database" window, select the files in the new path and hit "OK".