How to back up a database (SQL Server 2008) in C # without using SMO?

Question

I have this code and it does not work, but I do not know 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:

What is the problem?

Answer 1

You need to assign this SqlConnection object to 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"); } } } 

Answer 2

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

Answer 3

Sql Server 2008 fallback 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); } }