SQL Server Default File Locations – Data, Transaction Log and Backup

Problem

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?

Solution

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.

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.

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

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:

CREATE DATABASE TestDatabase
Go

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

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

Here is the query output.

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]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\SQLDatabases\DataFiles'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\SQLDatabases\LogFiles'
GO

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

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.

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

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

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.

Summary

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

• To learn more about restarting SQL Server: How to stop and start SQL Server services.
• To learn more about database files: Understanding how SQL Server stores data in data files.
• In an upcoming article, we will learn how to change the default location of database files in Linux.