Sql-server – How to move folder of SQL Server LocalDB instances from the default location

sql-server-expresssql-server-localdbssms

I get multiple errors with 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).

Any help would be appreciated.

Edit:
The Database Settings page KookieMonster mentioned previously 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 is denied.' (Microsoft SQL
Server, Error: 22002)

I get this even if the folder has Full control set for Everyone! Even more amusing the only folder that works, one that is set as a default (the user account's root) has no special permissions applied – just the ones my user has (just as any other folder I tried to set). In effect this means that all my LocalDB databases' files are saved to my account's root folder.

Best Answer

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

Related Solutions

How to Remove SQL Server Express Instances and Upgrade to Standard/Developer/Enterprise

(Local) without an instance name is the instance named MSSQLSERVER (the "SQL Server (MSSQLSERVER)" service).

If your only goal here is to get SSMS and SQL Profiler from the normal edition and not the express edition of SSMS that's easy, just install the tools from the Developer edition DVD / ISO.

Launch the installer, get through to the list of services. Select only the management studio options (there are two, one under the other) and finish the wizard. This will give you the tools and leave your instances alone.

SQL Server Express 2008 – Storing Database Files Outside Default Directory

You can place your database files anywhere you want, you just have to specify these locations within SQL Server itself. There are two ways to relocate those files.

Create in a new location

When you create a database or add a file to the database, you can specify the new location and the file will be created there. For example, if I wanted to add a new data file to my database, I would do the following

ALTER DATABASE [foo]
ADD FILE (name=foo_data_02, filename='X:\YourCustomLocation\foo_data_02.ndf');

As long as your SQL Server Service Account has access to the path, whatever you put after 'filename' will be where your file is created.

Move to a new location

You can also move an existing data file to a new location. This requires a couple steps. The first is to change the path within SQL Server first, like so:

ALTER DATABASE [foo]
MODIFY FILE (name=foo_data_01, filename'X:\YourCustomLocation\foo_data_01.mdf`)

By modifying this, the next time this database starts up it will look for the file in that location. Once you've changed it, you will actually need to move the file. To do this, take the database offline:

ALTER DATABASE [foo]
SET OFFLINE WITH ROLLBACK IMMEDIATE;

Once the database is offline, then copy the file to the new location using your preferred method. Once it has been copied, bring your database online:

ALTER DATABASE [foo]
SET ONLINE;

At this point, your database will now be running with your files in the new location.

Best Answer

Related Solutions

How to Remove SQL Server Express Instances and Upgrade to Standard/Developer/Enterprise

SQL Server Express 2008 – Storing Database Files Outside Default Directory

Related Question