How to backup - restore .mdf database

Question

I have developed a windows application with vs2010 and c#. I would like to know a way to backup and restore my local mdf database programmatically. With sdf database I use File Copy but it doesn't seem to work with mdf files. Can anyone help?

Answer 1

I struggled a lot with this, and the accepted answer does not do the trick, so here is a solution that worked for me (thanks to dnxit)

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

Answer 2

Try in this way:

• Go to Sql Management Studio and select the database you want to backup
• Right click and select 'Tasks' -> 'Backup'
• Adjust the parameters as you like, but don't confirm the dialog
• Press the button SCRIPT and dismiss the dialog

• On the query window insert the following text before the backup command

  CREATE PROCEDURE DO_BACKUP  
  AS  
  BEGIN  
     -- HERE GOES THE BACKUP TEXT CREATED BY THE SCRIPT BUTTON   
     -- FOR EXAMPLE
     BACKUP DATABASE [Customers] 
     TO DISK = N'E:\backups\customers.bak' 
     WITH NOFORMAT, NOINIT,  
     NAME = N'Customers - Full Database Backup', 
     SKIP, NOREWIND, NOUNLOAD,  STATS = 10
  END  
  

and execute (selecting the correct database) using the exclamation mark button.

Now you have a stored procedure called DO_BACKUP that you can call from your code using the normal ADO.NET objects like SqlConnection and SqlCommand