How to backup a SQL Server 2014 Express Localdb (.mdf) file programmatically

Question

I have simple Windows application which uses SQL Server 2014 LocalDB (.mdf file).

And I want that whenever users click exit button, my application automatically backup its localdb file (.mdf) to another folder in the same computer of users.

I wrote below simple code but a SQLException syntax error occurred:

Incorrect syntax near`'C:\greendb_angelheart.mdf'

(DATABASE ""{0}"" syntax seems fine)

And I'm worried whether it's right to connect to the specific localdb file by using normal SqlConnection code.

My simple code is:

private void Window_Closing(object sender, System.ComponentModel.CancelEventArgs e)
{
    if (MessageBox.Show("Really want to exit? Thank you !", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.No)
    {
            e.Cancel = true;
    }
    else
    {
            string backuppath_basic = @"c:\Green_Backup";

            if (!System.IO.Directory.Exists("backuppath_basic"))
            {
                System.IO.Directory.CreateDirectory(backuppath_basic);
            }

            var greendbfileName = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), string.Format("greendb_{0}.mdf", personID));
            var copied_greendbfileName = string.Format(@"C:\greendb_{0}.mdf", personID);

            string localConnectionString = string.Format(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename= " + Environment.GetEnvironmentVariable("APPDATA") + @"\greendb_{0}.mdf;Integrated Security=True;Connect Timeout=30;", personID);

            SqlConnection backupConn = new SqlConnection();
            backupConn.ConnectionString = localConnectionString;
            backupConn.Open();

            SqlCommand backupcomm = backupConn.CreateCommand();
            string backupdb = @"BACKUP DATABASE ""{0}"" TO DISK '{1}'";
            backupdb = string.Format(backupdb, greendbfileName, copied_greendbfileName);

            SqlCommand backupcreatecomm = new SqlCommand(backupdb, backupConn);
            backupcreatecomm.ExecuteNonQuery();

            backupConn.Close();

            Environment.Exit(0);
        }
    }

I think you cant copy mdf,ndf or ldf files while SQL Server Service is running. Did you try to take database offline and then copy? Or Detach - Copy - Attach? Or why you not take a simple full backup? — Yusif Yusifov, Mar 04 '16 at 06:09
@YusifYusifov, Thanks for your comment, when I tried to simply copy when my application is running, I got IOException that the process cannot access databasefile because another process is using the file. That's why I asked this question..SQL syntax error seems usually easy but I failed to get right syntax even though I tried many scenarios like single quote, double quote, without quote and so on...Any good idea would be highly appreciated ! — Kay Lee, Mar 04 '16 at 09:50

dnxit · Answer 1 · 2017-10-13T22:19:25.823

it may help someone.

Backup

try
{
    var dlg = new System.Windows.Forms.FolderBrowserDialog();
    var result = dlg.ShowDialog(this.GetIWin32Window());

    if (result.ToString() == "OK")
    {
        var dbfileName = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "LibraryManger.mdf");
        var backupConn = new SqlConnection { ConnectionString = eb.GetConnectionString() };
        backupConn.Open();

        var backupcomm = backupConn.CreateCommand();
        var backupdb = $@"BACKUP DATABASE ""{dbfileName}"" TO DISK='{Path.Combine(dlg.SelectedPath,"LibraryManagement.bak")}'";
        var backupcreatecomm = new SqlCommand(backupdb, backupConn);
        backupcreatecomm.ExecuteNonQuery();
        backupConn.Close();

        MessageBox.Show($"Database backup has successfully stored in {Path.Combine(dlg.SelectedPath, "LibraryManagement.bak")}", "Confirmation");
    }
}
catch (Exception ex)
{
    if(ex.Message.Contains("Operating system error"))
    {
        MessageBox.Show("Please chose a public folder.", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
    }
    else
        MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}

Restore

You'll have to close existing connection before you restore

try
{
    if (eb != null)
    {
        eb.DisposeConnection();
        eb = null;
    }

    var dlg = new OpenFileDialog();
    dlg.InitialDirectory = "C:\\";
    dlg.Filter = "Database file (*.bak)|*.bak";
    dlg.RestoreDirectory = true;

    if (Equals(dlg.ShowDialog(), true))
    {
        using (var con = new SqlConnection())
        {
            con.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;Database=Master;Integrated Security=True;Connect Timeout=30;";
            con.Open();
            var dbfileName = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "LibraryManger.mdf");
                using (var cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandText = $@"RESTORE DATABASE ""{dbfileName}"" FROM DISK='{dlg.FileName}'";

                    cmd.ExecuteNonQuery();
                }
                con.Close();
            }

        MessageBox.Show($"Database backup has successfully restored.", "Confirmation");
        eb = new EntityBroker.EntityBroker();
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}

Kay Lee · Accepted Answer · 2016-03-06T07:40:13.963

I finally solved after many trial and analysis. For someone who is looking for solutions, I share mine as below.

It seems there's fewer people are developing with MS SQL Localdb than other databases.

The name of database doesn't have to include extension like .mdf and the equal sign= has to be together as DISK=

string backupdb = string.Format(@"BACKUP DATABASE greendb_{0} TO DISK='c:\Green_Backup\greendb_{0}.bak'", personID);

How to backup a SQL Server 2014 Express Localdb (.mdf) file programmatically

2 Answers2

Linked