CODEWITHSUNDEEP
c#sql-serverdatabasebackup
Saleh
Saleh

Reputation: 2719

How to backup database (SQL Server 2008) in C# without using SMO?

I have this code and it is not working but I don't why?

try
{
   saveFileDialog1.Filter = "SQL Server database backup files|*.bak";
   saveFileDialog1.Title = "Database Backup";

   if (saveFileDialog1.ShowDialog() == DialogResult.OK)
   {
      SqlCommand bu2 = new SqlCommand();
      SqlConnection s = new SqlConnection("Data Source=M1-PC;Initial Catalog=master;Integrated Security=True;Pooling=False");

      bu2.CommandText = String.Format("BACKUP DATABASE LA TO DISK='{0}'", saveFileDialog1.FileName);

      s.Open();

      bu2.ExecuteNonQuery();
      s.Close();

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

and I get this error :

alt text

What is the problem?

Upvotes: 4

Views: 15420

Answers (3)

srishti
srishti

Reputation: 1

Backup Sql Server 2008 database in C#(100% correct) 

using System.Data.SqlClient;
try{
    SqlConnection con = new SqlConnection(cs.conn()); 
    string database = con.Database.ToString(); 
    string datasource = con.DataSource.ToString(); 
    string connection = con.ConnectionString.ToString(); 
    string file_name =data_loaction+database_name;
    --- "D:\\Hotel BackUp\\" + database + day + month + year + hour + minute + second + ms + ".bak";

    con.Close();
    con.Open();                

    string str = "Backup Database  [" + database + "] To Disk =N'" + file_name + "'";// With Format;' + char(13),'') From  Master..Sysdatabases  Where [Name] Not In ('tempdb','master','model','msdb') and databasepropertyex ([Name],'Status') = 'online'";

    SqlCommand cmd1 = new SqlCommand(str, con);
    int s1 = cmd1.ExecuteNonQuery();
    con.Close();               
    if (s1 >= -1)
            MessageBox.Show("Database Backup Sucessfull.", "Hotel Management", MessageBoxButtons.OK, MessageBoxIcon.Information);
    else{
            MessageBox.Show("Database Backup Not Sucessfull.", "Hotel Management", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
}

Upvotes: 0

marc_s
marc_s

Reputation: 755321

You need to assign that SqlConnection object to the SqlCommand - try this code:

if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
    string connStr = "Data Source=M1-PC;Initial Catalog=master;Integrated Security=True;Pooling=False";

    using(SqlConnection conn = new SqlConnection(connStr))
    {
       string sqlStmt = String.Format("BACKUP DATABASE LA TO DISK='{0}'", saveFileDialog1.FileName);

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

           MessageBox.Show("ok");
       }
    }
}

Upvotes: 5

Matti Virkkunen
Matti Virkkunen

Reputation: 65166

You never tell your SqlCommand which connection to use (this is what the error message says by the way, did you read it?). Either set the Connection property or use SqlConnection.CreateCommand to create the command in the first place.

Upvotes: 5

Related Questions