My Connection String in which error occurs

<add name="SmartSales_local" connectionString="Server=.\SQLEXPRESS;Integrated Security=true;AttachDbFileName=|DataDirectory|\SmartSales.mdf;"
          providerName="System.Data.SqlClient" />

My Database Backup Code in C#

            con.ConnectionString = MyConnection();
            String receiptdir = @"D:\SmatSalesReports\Backup";
            if (!Directory.Exists(receiptdir))
            {
                Directory.CreateDirectory(receiptdir);
            }
            string dbname = con.Database.ToString();
            
            string backupdb = "BACKUP DATABASE ["   dbname   "] TO DISK='"   receiptdir   "\\"   "SmartSales"   "-"   DateTime.Now.ToString("yyyy-MM-dd--HH-mm-ss")   ".bak'";
            
            
            con.Open();
            cm = new SqlCommand(backupdb, con);
            cm.CommandTimeout = 120;
            cm.ExecuteNonQuery();
            con.Close();

I am getting this error while trying to backup sql server database to disk. Everything else works fine "inserting/Updating/deleting" in the database. It gives me this error when I try to backup the database. Screenshot of the error described

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

New Information added after edit

My Connection String in which everything works fine

<add name="SmartSales" connectionString="Data Source=LAPTOP-GPJ5DM2V\SQLEXPRESS;Initial Catalog=SmartSales;Integrated Security=True"
      providerName="System.Data.SqlClient" />

When I use this connection string everything works fine. But it works when SSMS is installed and the DB is opened in SSMS.

CodePudding user response：

Adding to what @squillman suggested in the comment, you'll get that exact error with backup database [] to disk='yourpath.bak';. The connection database property isn't automatically set until the connection is opened so you need to move these lines after the Open:

con.Open();
string dbname = con.Database.ToString();
string backupdb = "BACKUP DATABASE ["   dbname   "] TO DISK='"   receiptdir   "\\"   "SmartSales"   "-"   DateTime.Now.ToString("yyyy-MM-dd--HH-mm-ss")   ".bak'";

CodePudding user response：

You have a number of issues going on here:

• Your primary issue: you are relying on the (actually empty) value of con.Database to set the database to backup. If you really needed to parse it out of the connection string, you could use SqlConnectionStringBuilder, or you could open the connection first
• You are using AttachDbFilename, which has been deprecated and is generally a bad idea
• The BACKUP command is fully able to be parameterized, you should do so
• You need to dispose your connection and command objects with using

String receiptdir = @"D:\SmatSalesReports\Backup";
if (!Directory.Exists(receiptdir))
{
    Directory.CreateDirectory(receiptdir);
}
            
const string backupdb = @"
BACKUP DATABASE @dbname TO DISK = @location;
";
var location = Path.Combine(receiptdir, @"\SmartSales-"   DateTime.Now.ToString("yyyy-MM-dd--HH-mm-ss")   ".bak");

using(var con = new SqlConnection(MyConnection())
using(var cm = new SqlCommand(backupdb, con) { CommandTimeout = 120})
{
    con.Open();
    cm.Parameters.Add("@dbname", SqlDbType.NVarChar, 128) = con.Database.ToString();
    cm.Parameters.Add("@location", SqlDbType.NVarChar, 255) = location;
    cm.ExecuteNonQuery();
}