THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

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 @HkeyLocal=N'HKEY_LOCAL_MACHINE'
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]'

image

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.

image

@Jamiet

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

Comments

 

Cliff said:

The folks over at SqlSlayer.com 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.

http://sqlandme.com/2012/01/05/sql-server-identifying-default-data-directory-for-multiple-instances-through-registry/

Thanks,

Vishal

December 12, 2012 2:28 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement