CODEWITHSUNDEEP
c#sql-serversql-server-2014-localdb
Kay Lee
Kay Lee

Reputation: 952

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

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);
        }
    }

Upvotes: 1

Views: 7696

Answers (2)

dnxit
dnxit

Reputation: 7350

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);
}

Upvotes: 3

Kay Lee
Kay Lee

Reputation: 952

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);

Upvotes: 1

Related Questions