How to delete, re-create, move, import, and export LocalDB databases?

Question

I am using C# WPF, Entity Framework, and MS SQL Server. My customer uses LocalDB.

I connect to my database through the following connection string:

"Server=(localdb)\MSSQLLocalDB; Database=Production; Integrated Security=True;"

And I found that the database is saved as two files: C:\Users\[username]\Production.mdf and Production_log.ldf.

Let's say these two files are lost. So, I want to create this database again and then import a database from another mdf-file. I run my C# project and when doing

using (ProductionContext db = new ProductionContext())
{
    db.Database.EnsureDeleted();
    db.Database.EnsureCreated();
}

the EnsureCreated() method gives me the following error:

Database 'Production' already exists. Choose a different database name

Also, it looks like the database mdf- and ldf-files are still there, inside

C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA

And it confuses me.

I tried to delete the mdf- and ldf-files in the Program Files folder but it didn't help. I tried to stop, delete, and create a LocalDB instance through command prompt (sqllocaldb stop, sqllocaldb delete, sqllocaldb create) but it didn't help either. I also looked into the database's system views (e.g., sys.sysfiles) to locate the database's files but it gave me no additional info.

I had also problems with moving, importing, and exporting a localdb database.

The reason why I am asking this question is that I expect that my customers will definitely move databases from one location to another within the same PC and between PCs. I also want to provide a good backup system. So, I need to be able to do simple CRUD-actions and have full control over database files.

Answer 1

After a deeper research, I found out the following:

1. You can specify the folder where your database is stored, in a connection string, e.g.:

string myAppName = "MyAppName";
string myDatabaseName = "MyDatabaseName";
string appDataPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), myAppName);
string databaseFilePath = Path.Combine(appDataPath, myDatabaseName + ".mdf");
string connectionString = "Server=(localdb)\\" + myAppName + "; Database=" + myDatabaseName + "; Integrated Security=True; AttachDbFilename=" + databaseFilePath + ";"

2. Losing (accidentally deleting or moving) the database files leads to software exceptions. To avoid it and some other bad cases, you can make some preparations at the beginning of your program, e.g.:

   private void Prepare()
   {
       if (!Directory.Exists(appDataPath)) Directory.CreateDirectory(appDataPath);
   
       using (DbContext db = new DbContext())
       {
           try
           {
               CreateLocalDBInstance();
               db.Database.EnsureCreated();
           }
           catch
           {
               DeleteLocalDBInstance();
   
               CreateLocalDBInstance();
               db.Database.EnsureCreated();
   
               if (!db.Database.CanConnect())
               {
                   MessageBox.Show("Unable to connect to the database");
                   this.Shutdown();
                   return;
               }
           }
       }
   }
   
   private int ExecuteLocalDBCommandCMD(string arguments)
   {
       try
       {
           var proc = new Process
           {
               StartInfo = new ProcessStartInfo
               {
                   FileName = "sqllocaldb",
                   Arguments = arguments,
                   UseShellExecute = false,
                   RedirectStandardOutput = true,
                   CreateNoWindow = true
               }
           };
   
           proc.Start();
           proc.WaitForExit();
   
           return proc.ExitCode;
       }
       catch
       {
           return 1;
       }
   }
   
   private void CreateLocalDBInstance()
   {
       ExecuteLocalDBCommandCMD("create " + myAppName);
       ExecuteLocalDBCommandCMD("start " + myAppName);
   }
   
   private void DeleteLocalDBInstance()
   {
       ExecuteLocalDBCommandCMD("stop " + myAppName);
       ExecuteLocalDBCommandCMD("delete " + myAppName);
   }

3. If you want to move your database to a different location, import or export, you should use BACKUP and RESTORE functions, e.g.:

public void BackupDatabase(DbContext db, string path)
{
    db.Database.ExecuteSqlRaw("ALTER DATABASE " + myDatabaseName + " SET MULTI_USER WITH ROLLBACK IMMEDIATE");
    File.Delete(path);
    db.Database.ExecuteSqlRaw("BACKUP DATABASE " + myDatabaseName + " TO DISK='" + path + "'");
}

public void RestoreDatabase(DbContext db, string path)
{
    db.Database.EnsureDeleted();
    
    // You can't restore a database in use. You need to switch to another database first
    connectionStringMaster = "Server=(localdb)\\" + myAppName + "; Database=master; Integrated Security=True;";
    
    string? connectionString = db.Database.GetConnectionString();
    db.Database.SetConnectionString(connectionStringMaster);
    
    db.Database.ExecuteSqlRaw("RESTORE DATABASE " + myDatabaseName + " FROM DISK='" + path + "'");
    
    db.Database.SetConnectionString(connectionString);
}