Change the default SQL Server locations for database data files, transaction log files and backup files

By:   |   Updated: 2023-10-31   |   Comments   |   Related: More > Backup


I want to change the default disk location for new databases and backups. Is there a way that this can be done in SQL Server?


Recently, we encountered an issue where one of the storage volumes on the database server was full, and we had to add additional storage for the server. After configuring the storage, we decided to change the default location for new databases so that when a new client database is added the database files are created on the new storage drives if the path is not used. The best method is to always include the path for the database files and backup files, but if it is not used these default locations will at least put the files where you want them to go.

In this article, we look at three ways to do this. Note that all of these options require SQL Server to be restarted for the change to take effect. I have sysadmin access on SQL Server, so I decided to use the safest option which is using SQL Server Management Studio (SSMS).

In this tip, we will discuss three methods to change the default location of data files, transaction log files, and backup files:

  1. Using SSMS
  2. Using xp_instance_regwrite
  3. Changing the Windows registry

Let us explore each method.

Change SQL Server Database Default Location using SSMS

The database default locations can be changed from the Server Properties option in SSMS.

To do that, open SSMS and connect to the SQL Server instance. Right-click on the server name (Nisarg-PC in my case) and select Properties.

Change Database Default Location from SSMS

When the Server Properties dialog box opens, select Database Settings. You can view the list of database default locations on the right pan as shown below.

Change Database Default Location from SSMS

Enter the new location in the Data, Log, and Backup textboxes and click OK.

Change Database Default Location from SSMS

NOTE: You must restart the SQL Server service to apply the changes otherwise the old values will still be used. Also, if you go back into SSMS after making the changes but did not restart SQL Server the old values will still show in the GUI. When the changes are made they are written to the registry and SQL Server will read the new values from the registry the next time it is started.

Checking the Changes

Once the SQL Server service has been restarted, we will create a database named TestDatabase to verify the database files are created in the new location using the following query:


Next, verify the file locations by querying sys.database_files DMV using the query below:

use TestDatabase

select file_id, Type_Desc, name, physical_name, state, state_desc, size, max_size, growth 
from sys.database_files

Here is the query output.

Change Database Default Location from SSMS

As you can see, the database file is created in D:\MS_SQL\Data, and the transaction log file is created in the D:\MS_SQL\Log directory.

Change SQL Server Database Default Location using xp_instance_regwrite

We can use the xp_instance_regwrite stored procedure to change the default location of the data file and log file. This undocumented external stored procedure changes the Windows Registry values related to the SQL Server. Just a note of reference, the above option using the SSMS GUI actually uses xp_instance_regwrite. If you use SSMS to make the change and select the Script option you can see the actual commands that are used.

Since the xp_instance_regwrite is undocumented, you must find external (non-Microsoft official documents) resources to learn more about it, like this article: xp_instance_regwrite syntax.

We will change the data file location from D:\MS_SQL\Data\ to D:\SQLDatabases\DataFiles and the transaction file's location from D:\MS_SQL\Log to D:\SQLDatabases\LogFiles using the following script:

USE [master]
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\SQLDatabases\DataFiles'
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\SQLDatabases\LogFiles'

Once the registry is updated, restart SQL Server and verify the changes with the SSMS Server Properties as shown in the screenshot below.

Use xp_instance_regwrite System Stored Procedure

Change SQL Server Database Default Location using the Windows Registry

We can use the Windows Registry editor to change the default locations. To do that, open the Windows run command and enter regedit.exe.

In the registry editor, find \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\.

Under the above key, find the SQL Server instance and go to the MSSQLServer folder, you can see the entire path below. In this folder, you should see BackupDirectory, DefaultLog, and DefaultData keys and their values.

Note: if you have never changed the default values for the DefaultData or DefaultLog these keys may not show up in the registry, but the BackupDirectory key should be there regardless if you made a change or not.

Changing the Windows Registry

Double-click the DefaultData key and a dialog box opens where you can enter the appropriate Value and then click OK.

Changing the Windows Registry

Follow the same process to change the default location of the transaction log file or the BackupDirectory. See the following screenshot for reference.

Changing the Windows Registry

As with the other options, you need to restart SQL Server to apply the changes. Once the services have restarted, open Server Properties in SSMS to verify.

Changing the Windows Registry


This tip explains three methods that can be used to change the default location of data files, transaction log files, and backup files.

Using the SSMS GUI is probably the simplest way to do this, but if you need to script and automate the process using xp_instance_regwrite can make it much easier.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Nisarg Upadhyay Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.

View all my tips

Article Last Updated: 2023-10-31

Comments For This Article