database backup

Question

I looked in some tutorials here on the site, but I did not find one that fits what I’m looking for, so I followed this tutorial Backup & Restore Sql Server database in C#

it even has a link in the comments with the codes already ready, but when I click to create the backup of DB appears this error:

I don’t know if it makes a difference, I don’t use Sql Server Management, direct use in Visual Studio 2010, anyone can tell me how to solve the problem?

PS1:

        private void btn_criar_Click(object sender, EventArgs e)
    {
        string database = con.Database.ToString();
        try
        {
            if (path_criar.Text == string.Empty)
            {
                MessageBox.Show("please enter backup file location");
            }
            else
            {
                string cmd = "BACKUP DATABASE [" + database + "] TO DISK='" + path_criar.Text + "\\" + "database" + "-" + DateTime.Now.ToString("yyyy-MM-dd--HH-mm-ss") + ".bak'";

                using (SqlCommand command = new SqlCommand(cmd, con))
                {
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    command.ExecuteNonQuery();
                    con.Close();
                    MessageBox.Show("database backup done successefully");
                    btn_criar.Enabled = false;
                }
            }

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

the part of line 54 is this command.ExecuteNonQuery();

PS2: I found out that if I click on the backup again soon after closing the error, it either backs up or restores depending on what I choose, then I "engemble" it, put in "catch" a btn_create.Performclick(); for it to click the button again instead of the error appear, but I would like to solve the problem because I don’t know what problems can happen in the future (and yes, I have tried it and the backup works)

Answer 1

On a Server it is common to use the SMO library for backup or restoration. SMO | SQL Server Management Objects contains numerous classes that facilitated and greatly managed a bank in a programmatic way. If you are interested see here: Documentation SMO Microsoft

I designed a very yes more and effective example to backup a DB LOCAL.

After creating a project, I added a bank. mdf to the project and left with default name Database1.mdf, added a new query, created a table and then leave for the same syntax used by vc: BACKUP DATABASE - TO DISK=''. see the image below.

Here I came across two problems:

• Estate Conexao.Database returning to me null;
• Datatime.Now.Tostring() generating syntax error;

Note that I have a query name string commented that was my first attempt, where I came across the 2 problems.

The solution I found was to use Application.Startuppath, to return me the way to db by adding its name right away. the 2 problem that affected the syntax were the date and time separators used in the return format.

string query = $"BACKUP DATABASE [{Application.StartupPath}\\DATABASE1.MDF] TO DISK ='{textBox1.Text}\\Database1_{DateTime.Now.ToString("yyyyMMdd_HHmmss")}.bak'";

Note that it worked perfectly, in the image above in the desktop folder screen I have 3 backup, although 2 are a little higher. every click generates a new one, I don’t have to override it because the DateTime will never return the nanosecond msm.