TSQL - An object or column name is missing or empty .. When trying to backup sql server database to disk

Question

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.

Put a breakpoint on the line with `con.Open()` and look at the value of `backupdb` at that point. Is it valid SQL and does it run in SSMS? The error doesn't sound like it's related to the code you've posted. Are you sure you don't have some event handlers mixed up in your code? — squillman, Oct 26 '21 at 18:08

score 0 · Answer 1 · answered Oct 26 '21 at 18:18

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'";

score 0 · Accepted Answer · answered Oct 26 '21 at 19:19

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();
}

TSQL - An object or column name is missing or empty .. When trying to backup sql server database to disk

2 Answers2