patternModerate
Move folder of SQL Server LocalDB instances from the default location
Viewed 0 times
theinstancessqllocaldbmovedefaultfolderserverfromlocation
Problem
I get multiple errors with
Now a simple DDL table creation script works fine from SQL Server Management Studio and creates the database in the root of my user account's folder (as explained here). The Instances folder however (located at
I'm using SQL Server Express 2012 and also SSMS of the same version (latest updates installed).
The Database Settings page threw an error for me, not being able to edit nor see any configuration there. I let Windows Update check for updates again and there were some updates for SQL Server Express that weren't installed (although I'm sure I selected everything to install before), so I installed them. This made it possible for me to go to the settings page.
I've tried to set up the LocalDB instance's root folder to somewhere else but I get an access denied error for every folder I tried, namely
Alter failed for Settings
'Microsoft.SqlServer.Management.Smo.ObjectKeyBase'.
(Microsoft.SqlServer.Smo)
------------------------------ ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
RegCreateKeyEx() returned error 5, 'Access
LocalDB (startup error, can't create DB from SQL Server Management Studio and also multiple issues when trying to restore a backup) and none of the fixes I found are helping anything. It seems to me that all errors have their root in permissions not set up correctly.Now a simple DDL table creation script works fine from SQL Server Management Studio and creates the database in the root of my user account's folder (as explained here). The Instances folder however (located at
D:\Users\[My name]\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances despite when installing the SQL Server Express with Advanced Services I specified the DATA folder in another location - where only standard SQL Server Express .mdf files are stored, but not LocalDB instances) seems problematic regarding permissions (in contrary to the account root) therefore and also for having my DB files in a location among my projects I'd like to move the Instances folder to another place. Is this possible? I haven't even found a corresponding registry entry.I'm using SQL Server Express 2012 and also SSMS of the same version (latest updates installed).
The Database Settings page threw an error for me, not being able to edit nor see any configuration there. I let Windows Update check for updates again and there were some updates for SQL Server Express that weren't installed (although I'm sure I selected everything to install before), so I installed them. This made it possible for me to go to the settings page.
I've tried to set up the LocalDB instance's root folder to somewhere else but I get an access denied error for every folder I tried, namely
Alter failed for Settings
'Microsoft.SqlServer.Management.Smo.ObjectKeyBase'.
(Microsoft.SqlServer.Smo)
------------------------------ ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
RegCreateKeyEx() returned error 5, 'Access
Solution
I used Process Monitor to trace the registry access and found it was getting an Access Denied while trying to write:
I got around this by opening the Registry Editor and giving Everyone full access to
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
Context
StackExchange Database Administrators Q#39410, answer score: 17
Revisions (0)
No revisions yet.