Maintenance: Move Data / Log file location in SQL Server using Detach & Attach functionality.

Here we are going to move the transaction log physical location of database 'TEST1'.

1) Use following query to get the data file & log file locations,

 


USE [TEST1]
GO
sp_helpfile --Here the log location is 'E:\TransactionLogs\TEST1_Log.ldf'
GO

Output:

2) Now we got the current location of log file for database TEST1 as 'E:\TransactionLogs\TEST1_Log.ldf'. I'm moving the file location from 'E:\TransactionLogs\TEST1_Log.ldf' to different drive as 'L:\TransactionLogs\TEST1_Log.ldf'

3) First you need to set the database in single user mode to restrict any open connections to database. We use following query to do that,

 


ALTER DATABASE [TEST1] SET SINGLE_USER
GO

4) Once the database is set to single user mode detach the database using following query,

 
USE master
GO

sp_detach_db [TEST1]
GO

5) Once the database is successfully detached move the file from original location to required target location. Here we are moving to target location 'L:\TransactionLogs\TEST1_Log.ldf'.

6) Once the file has been moved to target location, use following command to re-attach the database,

 
USE master
GO

sp_attach_DB [TEST1], 
'D:\MSSQL\DATA\TEST1_Data.mdf',
'L:\TransactionLogs\TEST1_Log.ldf'
GO

7) Now verify whether the file has been successfully moved to new location with same query from step (1) as below,

USE [TEST1]
GO

sp_helpfile
GO

Output:

8) Note - Here we have moved only log file but you can follow same steps to change data file location as well.