Default Directories

You decide to create a new database, or restore one for that matter, and you find that it creates or restores the database to a file location you didn’t expect. Why did the data file end up on the C: drive? Why did the transaction log file end up in the same location as the data file.

In this post, I’m talking about the default directories for data, transaction log and backup files. How to check what they are, and how to change em.

How to check what the default directories are?

If you prefer using the SSMS GUI, connect to the SQL Server instance, and right click the SQL Server instance name in Object Explorer and select Properties, then find the Database Settings page. Here you’ll see them displayed for you under the Database default locations section. It shows the default directories for database data files, database transaction log files, and backup files:

Alternatively, you can run a query using the xp_instance_regread stored procedure:

EXECUTE xp_instance_regread 
	@rootkey = N'HKEY_LOCAL_MACHINE', 
	@key = N'Software\Microsoft\MSSQLServer\MSSQLServer', 
	@value_name = N'DefaultData'
	
EXECUTE xp_instance_regread 
	@rootkey = N'HKEY_LOCAL_MACHINE', 
	@key = N'Software\Microsoft\MSSQLServer\MSSQLServer', 
	@value_name = N'DefaultLog'

EXECUTE xp_instance_regread 
	@rootkey = N'HKEY_LOCAL_MACHINE', 
	@key = N'Software\Microsoft\MSSQLServer\MSSQLServer', 
	@value_name = N'BackupDirectory'

This will output the three default directories:

So, now you want to change the default directories

Using the SSMS GUI again, in the same Server Properties window that I opened earlier, you can select the “…” buttons and browse to the desired folder in the Locate Folder window. Or, you can simply change the path directly in the textboxes.

Changing the default directory using the code is just as easy as reading what the directories are as we did before. You can run the below code using the xp_instance_regwrite stored procedure to change the values:

-- Set the default data directory
EXECUTE xp_instance_regwrite 
	@rootkey = N'HKEY_LOCAL_MACHINE', 
	@key = N'Software\Microsoft\MSSQLServer\MSSQLServer', 
	@value_name = N'DefaultData', 
	@type = REG_SZ, 
	@value = N'F:\MSSQL\New Location\Data'
	
-- Set the default log directory
EXECUTE xp_instance_regwrite 
	@rootkey = N'HKEY_LOCAL_MACHINE', 
	@key = N'Software\Microsoft\MSSQLServer\MSSQLServer', 
	@value_name = N'DefaultLog', 
	@type = REG_SZ, 
	@value = N'F:\MSSQL\New Location\Log'	

-- Set the default backup directory
EXECUTE xp_instance_regwrite 
	@rootkey = N'HKEY_LOCAL_MACHINE', 
	@key = N'Software\Microsoft\MSSQLServer\MSSQLServer', 
	@value_name = N'BackupDirectory', 
	@type = REG_SZ, 
	@value = N'F:\MSSQL\New Location\Backup'

If we run the same query as before to confirm the new locations, we now get:

Checking multiple instances

That’s all good and well for one SQL Server instance, but if you want to check the default directories across many instances, then you don’t want the code outputting 3 different result sets in the query window. You can add an output variable to each of the regread executions and save the values to a variable. I’ve also added a server name column so it’s easy to tell what SQL Server instance the default directories are for.

DECLARE @DefaultDataDir NVARCHAR(260)
DECLARE @DefaultLogDir NVARCHAR(260)
DECLARE @DefaultBackupDir NVARCHAR(260)

EXECUTE xp_instance_regread 
	@rootkey = N'HKEY_LOCAL_MACHINE', 
	@key = N'Software\Microsoft\MSSQLServer\MSSQLServer', 
	@value_name = N'DefaultData',
	@value = @DefaultDataDir OUTPUT
	
EXECUTE xp_instance_regread 
	@rootkey = N'HKEY_LOCAL_MACHINE', 
	@key = N'Software\Microsoft\MSSQLServer\MSSQLServer', 
	@value_name = N'DefaultLog',
	@value = @DefaultLogDir OUTPUT

EXECUTE xp_instance_regread 
	@rootkey = N'HKEY_LOCAL_MACHINE', 
	@key = N'Software\Microsoft\MSSQLServer\MSSQLServer', 
	@value_name = N'BackupDirectory',
	@value = @DefaultBackupDir OUTPUT

SELECT
	@@SERVERNAME		AS [SQLServerName],
	@DefaultDataDir		AS [DefaultDataDir],
	@DefaultLogDir		AS [DefaultLogDir],
	@DefaultBackupDir	AS [DefaultBackupDir]

This will output a nice result set of the default directories:

So now, you can run this query against multiple instances. There are many ways to do this, but the easiest way to do it is to use the Registered Servers in SSMS. Here, I have all my servers listed and I can right click the folder they are in and select New Query.

Copy the query into this query window. It’ll have a pink status bar down the bottom to indicate it’ll be running for multiple instances. When I execute the query, I get two rows in the results, one per server. Registered Servers adds in a Server Name column for me, hence the extra column on the left. I wouldn’t need my SQLServerName column if using this method to execute:

Gotchas

Sometimes the directories that have just been set, do not appear to have changed! I’ve found restarting the SQL Server service for the instance always fixes that problem. That’s not ideal when you’re running a production instance that can’t be restarted whenever you like. Generally though, this setting can wait until the next maintenance window to get the restart it needs for this setting to persist, just be patient.