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

« SQL Server – Automating Backup Files Cleanup Task Using Maintenance Plans

SQL Server – Why is the ‘sa’ Login Account Disabled & How to Enable ‘sa’ Login Account »

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

August 10, 2013 by Suresh Raavi

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 secnarios 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 for every 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');

Note: Same method can be used for moving files for any system or user defined database except for Resource database files

Posted in Disk Space, Files and FileGroups, T-SQL, Transaction Log | Tagged SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | 17 Comments

17 Responses

on January 30, 2014 at 6:24 am | Reply saliksaly

Reblogged this on saliksaly.
on April 9, 2014 at 6:10 am | Reply Moving Databases in SQL: Two Simple Methods | Steadily Succeeding

[…] you to Suresh Raavi, who originally posted on this topic on […]
on July 8, 2014 at 5:42 am | Reply nagarajkt

thanks a lot its worked
on October 1, 2014 at 9:56 pm | Reply Ban Ăn Chơi

Thanks, nice post
on October 23, 2014 at 4:02 pm | Reply Les Ferguson

Does anything else need to be done if one of the files is moved to a different drive? Can’t get it to recognize a log file after moving from F: to C:
on November 15, 2014 at 2:53 am | Reply mohammad

thanks a lot..

I would like to know when the database comes to online what are the recovery phases will work and in which order they will come to online.
on November 27, 2014 at 12:35 am | Reply Atif

Thanks. Elaborated very effectively and step by step.
on February 7, 2015 at 2:12 pm | Reply sshdba

Reblogged this on SIMPLE SQL DBA.
on June 19, 2015 at 2:22 am | Reply Jamal ahmad

very very help full post
on August 11, 2015 at 5:16 am | Reply Dinh Vuong

How to add database file when I haven’t file?
Could you please send to me this file!
My email: dinhvuong2008@gmail.com
Thanks and best wish for you!!!
on September 7, 2015 at 1:20 am | Reply Arun Alex

Thank you
on April 6, 2016 at 11:36 am | Reply jag

after doing all these steps, Database in recover pending mode. how to solve this issue?
on May 23, 2016 at 11:42 pm | Reply How To View Sql Database Files | Castarwar

[…] SQL Server – How to Move Database Files – Data File and … – SQL Server – How to Move Database Files – Data File and Log File – From One Drive Location to the Other. August 10, 2013 by Suresh Raavi […]
on June 13, 2016 at 10:24 pm | Reply voy

One step that is missing when moving to new location is the new folder requires correct permissions for SQL Server to bring the database online.
https://msdn.microsoft.com/en-au/library/jj219062.aspx
on August 31, 2016 at 1:03 am | Reply Bull Fulbright

Great post!

You should change the default database path for new databases. The default path is obtained from the Windows registry.

You can also change with T-SQL, for example, to set default destination to: D:\MSSQLData

USE [master]

GO

EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’DefaultData’, REG_SZ, N’D:\MSSQLData’

GO

EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’DefaultLog’, REG_SZ, N’D:\MSSQLData’

GO

Extracted from: http://www.sysadmit.com/2016/08/mover-base-de-datos-sql-server-a-otro-disco.html
on November 9, 2016 at 10:59 am | Reply Mirza Baig

This does not work for me.
I cant change the file location once the database is offline.
How are users saying this has worked for them?
on February 28, 2017 at 8:00 am | Reply How To Change Database File Location In Sql Server | Information

[…] SQL Server – How to Move Database Files – Data File and … – SQL Server – How to Move Database Files – Data File and Log File – From One Drive Location to the Other. August 10, 2013 by Suresh Raavi […]