See my comment above. I used Process Monitor to trace the registry access and found it was getting an Access Denied while trying to write:
3:16:40.8405491 PM sqlservr.exe 8756 RegCreateKey HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer ACCESS DENIED Desired Access: Write
I got around this by opening the Registry Editor and giving Everyone full access to HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer
. Then I changed the default location and removed the Everyone access once it was done. This made the following extra keys that weren't there before:
DefaultData
DefaultLog
BackupDirectory
Your current method won't work, because it assumes that master
was placed in the default data/log path, that it hasn't been moved at any point, and that the defaults haven't been changed since then.
The registry approaches work, but they are kind of tedious (especially if you don't use the instance-specific XPs and have to provide the registry path yourself), and who knows if this is where the locations will always be stored.
A different approach - assuming model doesn't have ridiculous file size settings - is to just create a database temporarily and see where SQL Server puts it by default.
USE master;
GO
CREATE DATABASE [!#floobernuggets];
GO
SELECT
[type] = CASE [type] WHEN 1 THEN 'data' ELSE 'log' END,
[path] = SUBSTRING(physical_name, 1,
CHARINDEX(N'!#floobernuggets', physical_name)-1)
FROM sys.master_files
WHERE database_id = DB_ID(N'!#floobernuggets')
ORDER BY [type];
GO
DROP DATABASE [!#floobernuggets];
GO
(This also assumes your default path doesn't actually contain the string !#floobernuggets
already, and that you don't have any weird alerts or anything triggered off the actual creation of a database.)
Of course as Kin points out in SQL Server 2012 you will be able to use SERVERPROPERTY
for this.
SELECT [data] = SERVERPROPERTY('InstanceDefaultDataPath'),
[log] = SERVERPROPERTY('InstanceDefaultLogPath');
Best Answer
If using SSMS with a full install of SQL Server then there is a good answer on Stackoverflow.
If using SSMS as a standalone then the following should work