How to change path from c:\Users\XYZ\AppData\Local\...\LocalDB\ to other drive

Question

Hello,

due low disk space is required to change storage files (mdf, ldf and log) from yet working directory (e.g. c:\Users\XYZ\AppData\Local...\LocalDB) to other larger disk (e.g. D:\Temp\Local...\LocalDB\

1. How to do it on working system (SQL can be stopped during change directory path), please?
2. How to set an other like standard directory path for new system with fresh installation?

Answer 1

To move a database file, use this command:

ALTER DATABASE yourdb (NAME = '<logicalname>', FILENAME = '<newpath>)

Where logicalname is what you see in the first column in the output from sp_helpdb and newpath is where you want the file to be located.

SQL Server will not move the file for you, but you need to stop LocalDB and then move the files yourself. Then start LocalDB again.

To set the default directories, connect to the server in Object Explorer in SSMS, right-click the server itself and select properties. Go to the Database Settings page:

I will need to add the disclaimer that I have not verified that this actually works for LocalDB.

Answer 2

To change the storage path for SQL Server LocalDB database files (mdf, ldf, and log) on a working system, you can follow these steps:

1. Stop the LocalDB instance if it's running. You can do this by using the command:

      SqlLocalDB stop <InstanceName>
   

   Replace <InstanceName> with the name of your LocalDB instance.
2. Create the new directory where you want to move the database files. For example:

      mkdir D:\Temp\LocalDB
   

3. Move the existing database files from the old location to the new location. You can do this using File Explorer or command line commands.
4. Update the LocalDB instance to point to the new directory. You can do this by using the following command:

      SqlLocalDB set <InstanceName> -d D:\Temp\LocalDB
   

   This command sets the new directory for the instance.
5. Start the LocalDB instance again:

      SqlLocalDB start <InstanceName>
   

For a fresh installation of SQL Server LocalDB, you can set the default directory during the installation process or configure it afterward using the mssql-conf tool or by changing the settings in the LocalDB configuration files. However, specific steps for fresh installations are not detailed in the provided context.

---

References:

• SQL Server Express LocalDB