2

I am building a C# application with SQL Server 2008 R2 database which is stored in the bin folder of my project. I use Linq to Sql method to create the database and attach it to my project.

The problem that I'm having is that when I'm trying to create a backup of my database. It throws an error saying

Database (database_name) does not exist make sure the name is entered correctly. BACKUP DATABASE is terminating abnormally.

Here is the code that i write on my button click event:

try
{
    SaveFileDialog sd = new SaveFileDialog();
    sd.Filter = "SQL Server database backup files|*.bak";
    sd.Title = "Create Database Backup";

    if (sd.ShowDialog() == DialogResult.OK)
    {
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            string sqlStmt=string.Format("BACKUP DATABASE <database_name> TO DISK='{0}'",sd.FileName);

            using (SqlCommand bu2 = new SqlCommand(sqlStmt, conn))
            {
                conn.Open();
                bu2.ExecuteNonQuery();
                conn.Close();

                MessageBox.Show("Backup Created Sucessfully");
            }
        }
    }
}
catch(Exception ex)
{
    MessageBox.Show(ex.ToString());
}

My Connection String:

string connStr = ConfigurationManager.ConnectionStrings["project_name.Properties.Settings.project‌​_nameConnectionString"].ConnectionString;

and then from my app.config file 

<add name="project_name.Properties.Settings.project_nameConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\database_name.mdf;Integrate‌​d Security=True;User Instance=True" providerName="System.Data.SqlClient" />

so if any one have a solution to my problem that will be helpful.

Improve this question
3
  • The following question's answer seems to answer this question: stackoverflow.com/questions/4466428/… on the off chance it does not fully answer the question provide some additional information. Commented May 25, 2013 at 5:26
  • If you're using the AttachDbFileName= approach in your connection string, you cannot use BACKUP DATABASE since that database file is not really attached to the SQL Server Express instance. That's one of the many drawbacks and shortcomings of that approach Commented May 25, 2013 at 5:27
  • what additional detials do u require @Ramhound & here is my connection string ; string connStr = ConfigurationManager.ConnectionStrings["project_name.Properties.Settings.project_nameConnectionString"].ConnectionString; and then from my app.config file <add name="project_name.Properties.Settings.project_nameConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\database_name.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> Commented May 25, 2013 at 5:46

2 Answers 2

Reset to default
6

After a really long time brain storming, I finally succeded so, here is the solution: 

try
{
    SaveFileDialog sd = new SaveFileDialog();
    sd.Filter = "SQL Server database backup files|*.bak";
    sd.Title = "Create Database Backup";

    if (sd.ShowDialog() == DialogResult.OK)
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["project_name.Properties.Settings.project‌​_nameConnectionString"].ConnectionString))
        {
            string sqlStmt = string.Format("backup database [" + System.Windows.Forms.Application.StartupPath + "\\dbname.mdf] to disk='{0}'",sd.FileName);
            using (SqlCommand bu2 = new SqlCommand(sqlStmt, conn))
            {
                conn.Open();
                bu2.ExecuteNonQuery();
                conn.Close();

                MessageBox.Show("Backup Created Sucessfully");
            }                   
        }
    }
}
catch (Exception)
{
    MessageBox.Show("Backup Not Created");
}

Resource from this question: How to backup from .mdf database that I created

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

The whole User Instance and AttachDbFileName= approach is flawed - at best! When running your app in Visual Studio, it will be copying around the .mdf file (from your App_Data directory to the output directory - typically .\bin\debug - where you app runs) and most likely, your INSERT works just fine - but you're just looking at the wrong .mdf file in the end!

Also, since the .mdf file is not permanently attached to a SQL Server instance, you cannot use commands like BACKUP DATABASE on it.

In my opinion, the real solution would be to

  1. install SQL Server Express (and you've already done that anyway)

  2. install SQL Server Management Studio Express

  3. create your database in SSMS Express, give it a logical name (e.g. MyDatabase)

  4. connect to it using its logical database name (given when you create it on the server) - and don't mess around with physical database files and user instances. In that case, your connection string would be something like:

    Data Source=.\\SQLEXPRESS;Database=MyDatabase;Integrated Security=True

    and everything else is exactly the same as before...

And then, once you've done this, you can also run your backups using BACKUP DATABASE MyDatabase .... without any problems

Improve this answer

1 Comment

Thanks for helping @marc_s, now that i cannot use BACKUP DATABASE for my database until i create it in SSMS Express. Then for me i have to make a work around for my job like for BACKUP copying the database and adding it to compress (with save file dialog) and for RESOTRE unzipping the file from desired folder and adding it to application installed folder. Again thanks for helping everyone.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.