THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Query for server DefaultData & DefaultLog folders

Do you ever need to query for the DefaultData & DefaultLog folders for your SQL Server instance? Well, I just did and the following script enabled me to do that:

DECLARE @HkeyLocal NVARCHAR(18),@MSSqlServerRegPath NVARCHAR(31),@InstanceRegPath SYSNAME;

SELECT @MSSqlServerRegPath=N'SOFTWARE\Microsoft\MSSQLServer'
SELECT @InstanceRegPath=@MSSqlServerRegPath + N'\MSSQLServer'

DECLARE @SmoDefaultFile NVARCHAR(512)
EXEC MASTER.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'DefaultData', @SmoDefaultFile OUTPUT
DECLARE @SmoDefaultLog NVARCHAR(512)
EXEC MASTER.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'DefaultLog', @SmoDefaultLog OUTPUT

SELECT ISNULL(@SmoDefaultFile,N'') AS [DefaultFile],ISNULL(@SmoDefaultLog,N'') AS [DefaultLog]'


I haven’t done any rigorous testing or anything like that, all I can say is…it worked for me (on SQL Server 2012). Use as you see fit.

Doubtless this information exists in a multitude of other places but nevertheless I’m putting it here so I know where to find it in the future.

Just for fun I thought I’d try this out against SQL Azure Windows Azure SQL Database. Unsurprisingly it didn’t work there:

Msg 40515, Level 15, State 1, Line 16
Reference to database and/or server name in 'MASTER.dbo.xp_instance_regread' is not supported in this version of SQL Server.



Published Monday, December 10, 2012 1:10 PM by jamiet
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Cliff said:

The folks over at have an admin database put together that has some functions for getting default directories. Full Discolsure, it's run by friends of mine, and I;ve written a couple posts for the site. It is free and open source.

December 10, 2012 12:50 PM

Vishal said:

Handy script. I have also written a post earlier to extract default data paths for multiple instances.



December 12, 2012 2:28 AM

Leave a Comment


This Blog


Privacy Statement