Moving database files to a different location

Category : Tips User Rating : 4 Stars      Views : 4.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.

Sometimes it is necessary to relocate the physical files that comprise a SQL Server database. The recommended approach to moving the location of database files is using ALTER DATABASE commands.

Here we list the steps required to do this, using the AdventureWorks database as an example. You might want to take a backup of the database before moving, just in case !

To determine the current location of the files run the following :
SELECT name,physical_name
FROM AdventureWorks.sys.database_files
This lists all the data and log files associated with the database, together with their logical names (in this case there is one data and one log file): Move database file locations

Now that we know the logical names of the files we can set up the new locations of each file, using ALTER DATABASE :
MODIFY FILE (NAME = AdventureWorks_Data, FILENAME = 'D:\MSSQL\Data\AdventureWorks_Data.mdf');
MODIFY FILE (NAME = AdventureWorks_Log, FILENAME = 'D:\MSSQL\Log\AdventureWorks_Log.ldf');
This tells SQL Server where to find each file once they have been moved. Note that we haven’t actually moved the file at this stage, though the folders do need to exist or there will SQL Server will generate an error. You then need to take the database offline. To do this there must be no connections to it, you can check this using sp_who2 :
exec sp_who2
Once all connections have been removed you can then set the database to offline, using the following command (or right click on the database in SSMS and select ‘Take Offline’).
You can then physically move the file using Windows Explorer or similar. If you didn’t set the database to offline you will get an error message that the files are in use. Once the files have been moved set the database back online, using the following SQL (or using SSMS, as before) :
The database should now be up and running again, with the files in the new location !


As an alternative to using the ALTER DATABASE command many DBAs utilise an older method using stored procedures sp_detach_db and sp_attach_db, however Microsoft have announced that this will be withdrawn in a future version of SQL Server, so this method is not recommended.
Link back to this article : database files to a different location.aspx



Post by Bull Fulbright on Wed 31 Aug 2016 09:01. Report Inappropriate Post

Great post! Very useful!

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]


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


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


Extracted from:

Post by John Strode on Fri 19 Jan 2018 23:00. Report Inappropriate Post

If you change the default locations for the data file and the log file and then restore a file does it automatically place the data file and the log file into these paths as defined in the registry?
Post by sam on Sun 05 Apr 2020 21:08. Report Inappropriate Post
Website :
Msg 5120, Level 16, State 101, Line 46
Unable to open the physical file "D:\DATA\archdat1.mdf". Operating system error 5: "5(Access is denied.)".
Msg 5181, Level 16, State 5, Line 46
Could not restart database "Archive". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 46
ALTER DATABASE statement failed.

Post a comment   No login required !

Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered