how to backup and restore mdf file in winform C# after deployment project?

1

I am developing a desktop application in c# visual studio 2013, where I want to create a feature in which a user is allowed to restore and backup the Database by itself. but problem is that it doesn't backup or restore database after deploy project.

When I try to Backup it says!

Database 'DatabaseName' does not exit.Make sure the name is entered correctly. BACKUP DATABASE is terminating abnormally.

When I try to Restore Database it says

System.Data.Sqlclient.SqlException (0x80131904): User does not have permission to alter database .mdf', the database does not exit, or the the database in not in a state that allows access checks. and so on!

I am using SQL Server Express 2012 by attaching mdf file to the application, when i try to backup using query it works when i add Connection String through SQL Server but after attach mdf file i won't work .

have watch some tutorial videos and figured out some codes but I got nothing

here is my BACKUP code!

private void buttonbackup_Click(object sender, EventArgs e)
{
    try
    {

         SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\neyadatabase.mdf;Integrated Security=True;Connect Timeout=30");
         con.Open();

         String sql = "BACKUP DATABASE neyadatabase TO DISK = '" + backuploca.Text + "\\neyadatabase - " + DateTime.Now.Ticks.ToString() + ".Bak'";
          SqlCommand cmd = new SqlCommand(sql, con);
          cmd.ExecuteNonQuery();
          MessageBox.Show("backup done successfully!");
          con.Close();
          con.Dispose();
      }

      catch (Exception ex)
      {
          MessageBox.Show(ex.Message);
      }  
}

and here is my RESTORE Code!

private void buttonrestore_Click(object sender, EventArgs e)
{

    try
    {

         SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\neyadatabase.mdf;Integrated Security=True;Connect Timeout=30");
         con.Open();
         string sqlStmt2 = string.Format("ALTER DATABASE [neyadatabase.mdf] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
         SqlCommand bu2 = new SqlCommand(sqlStmt2, con);
         bu2.ExecuteNonQuery();

         string sqlStmt3 = "USE MASTER RESTORE DATABASE [neyadatabase.mdf] FROM DISK='" + restoreloca.Text + "'WITH REPLACE;";
         SqlCommand bu3 = new SqlCommand(sqlStmt3, con);
         bu3.ExecuteNonQuery();

         string sqlStmt4 = string.Format("ALTER DATABASE [neyadatabase.mdf] SET MULTI_USER");
         SqlCommand bu4 = new SqlCommand(sqlStmt4, con);
         bu4.ExecuteNonQuery();

         MessageBox.Show("database restoration done successefully");
         con.Close();

      }
      catch (Exception ex)
      {
         MessageBox.Show(ex.ToString());
      }


}

I am a beginner in this if anyone is going to help me please give some example too so that I can better understand!

Thank you

c#
sql
winforms
backup
restore
asked on Stack Overflow Aug 24, 2019 by Basit M • edited Aug 25, 2019 by theduck

1 Answer

0

For backup use :

private void buttonbackup_Click(object sender, EventArgs e)
{
    try
    {
        using (SqlConnection dbConn = new SqlConnection())
        {
             dbConn.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;Database=neyadatabase;Integrated Security=True;Connect Timeout=30;";
             dbConn.Open();

             using (SqlCommand multiuser_rollback_dbcomm = new SqlCommand())
             {
                 multiuser_rollback_dbcomm.Connection = dbConn;
                 multiuser_rollback_dbcomm.CommandText= @"ALTER DATABASE neyadatabase SET MULTI_USER WITH ROLLBACK IMMEDIATE";

                 multiuser_rollback_dbcomm.ExecuteNonQuery();
             }
             dbConn.Close();
        }

        SqlConnection.ClearAllPools();

        using (SqlConnection backupConn = new SqlConnection())
        {
            backupConn.ConnectionString = yourConnectionString;
            backupConn.Open();

            using (SqlCommand backupcomm = new SqlCommand())
            {
                backupcomm.Connection = backupConn;
                backupcomm.CommandText= @"BACKUP DATABASE neyadatabase TO DISK='c:\neyadatabase.bak'";
                backupcomm.ExecuteNonQuery();
            }
            backupConn.Close();
        }
    }

    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }  
}

And for restore :

private void buttonrestore_Click(object sender, EventArgs e)
{
    try
    {
        using (SqlConnection restoreConn = new SqlConnection())
        {
            restoreConn.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;Database=neyadatabase;Integrated Security=True;Connect Timeout=30;";
            restoreConn.Open();
            using (SqlCommand restoredb_executioncomm = new SqlCommand())
            {
                restoredb_executioncomm.Connection = restoreConn;
                restoredb_executioncomm.CommandText = @"RESTORE DATABASE neyadatabase FROM DISK='c:\neyadatabase.bak'";

                restoredb_executioncomm.ExecuteNonQuery();
            }
            restoreConn.Close();
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}
  • Backup and restore bak file. Mdf/ldf is for attaching not backup.

I hope this helps you. If you faced any problem let me know to update my answer ;). Good luck

answered on Stack Overflow Aug 25, 2019 by Mohamad Shahrestani • edited Aug 25, 2019 by Mohamad Shahrestani

User contributions licensed under CC BY-SA 3.0