Database Backup

Question

I have developed Windows forms program with c# and it uses a SQL Server database. I'm trying to have a button to make a backup file of the database, but I keep getting this error no matter what I do:

System.Data.SqlClient.SqlException: 'Incorrect syntax near '.'.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.'

I've tried:

 string nonquery = "BACKUP DATABASE DBMydatabase.mdf TO DISK = '@path' WITH STATS";

 string nonquery = @"BACKUP DATABASE DBMydatabase.mdf TO DISK = @path WITH STATS";

 string nonquery = "BACKUP DATABASE DBMydatabase.mdf TO DISK = @path WITH STATS";

 string nonquery = @"BACKUP DATABASE DBMydatabase.mdf TO DISK = '@path'";

 string nonquery = "BACKUP DATABASE DBMydatabase.mdf TO DISK = '@path'";

 string nonquery = "BACKUP DATABASE DBMydatabase.mdf TO DISK = @path";

My code is:

 string nonquery = @"BACKUP DATABASE DBMydatabase.mdf TO DISK = '@path' WITH STATS";
 connection.Open();
 SqlCommand command = new SqlCommand(nonquery, connection);
 command.Parameters.AddWithValue("@path", path);
 command.ExecuteNonQuery();

Answer 1

@J L, Welcome to Microsoft Q&A, based on the Microsoft Learning Example, we need to use database name instead of mdf file name to back up database.

Here is a code example you could refer to.

 private void button1_Click(object sender, EventArgs e)
        {
            string connstr = "connstr";
            SqlConnection connection = new SqlConnection(connstr);
            connection.Open();
            string path = @"C:\example\Test.bak";
            string sql =string.Format("BACKUP DATABASE Test To Disk='{0}' WITH STATS",path);
            SqlCommand command = new SqlCommand(sql, connection);
            command.ExecuteNonQuery();
            Console.WriteLine("11");
        }

Based on my test, it could generate the bak file in the correct path:

Hope my code could help you.

Best Regards,

Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

"BACKUP DATABASE DBMydatabase.mdf TO DISK = '@path' WITH STATS";

With a DATABASE BACKUP you backup the complete database, not a single database file MDF. Use the database name, not the database file name.