SQL Server – How to Move Database Files – Data File and Log File – From One Drive Location to the Other

Most of the DBAs at some point of time in their career should have faced at-least one of the below situation(s):

1. The disk/SAN where the database files are located is going to be replaced
2. Disk is full and no more free space available
3. Data files and/or log files are not located on standard drives

There can be even more scenarios  like the above where we may need to move the database files from current location to a new location. Starting SQL Server 2005 this can be simply achieved by using ALTER DATABASE T-SQL command.

Let us take the below scenario for step-by-step Demo:

Database: AdventureWorks2012
Current Files Location: C:\Disk1
New\Target Files Location: C:\Disk2

Step 1: Get the current database files Logical Name and Physical Location

USE master

GO

SELECT name AS LogicalFileName, physical_name AS FileLocation

, state_desc AS Status

FROM sys.master_files

WHERE database_id = DB_ID('AdventureWorks2012');

 Step 2: Take the Database offline

USE master

GO

ALTER DATABASE AdventureWorks2012 SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

Note: In the above T-SQL query, I used WITH ROLLBACK IMMEDIATE option. Please be careful when using this option as it rolls back those incomplete transactions immediately. You may exclude this option, but have to wait till all the transactions are committed to take the database offline.

Step 3: Move the database files physically in the Windows OS to the new location

Step 4: Use ALTER DATABASE to modify the FILENAME to new location forevery file moved

Only one file can be moved at a time using ALTER DATABASE.

USE master

GO

ALTER DATABASE AdventureWorks2012

MODIFY FILE 

( NAME = AdventureWorks2012_Data, 

FILENAME = 'C:\Disk2\AdventureWorks2012_Data.mdf'); -- New file path

USE master

GO

ALTER DATABASE AdventureWorks2012 

MODIFY FILE 

( NAME = AdventureWorks2012_Log, 

FILENAME = 'C:\Disk2\AdventureWorks2012_log.ldf'); -- New file path

Step 5: Set the database ONLINE

USE master

GO

ALTER DATABASE AdventureWorks2012 SET ONLINE;

Step 6: Now, verify the database files Physical location

USE master

GO

SELECT name AS FileName, physical_name AS CurrentFileLocation, state_desc AS Status 

FROM sys.master_files 

WHERE database_id = DB_ID('AdventureWorks2012');