Backup SQL Server database from windows form appli

Question

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

Want to improve this question? Update the question so it's on-topic for Stack Overflow.

Closed 7 years ago.

Improve this question

I need to backup a database by clicking a button on the windows application form. I'm developing it on Visual Studio 2012 in C#.

In Windows site, I learned to backup using Transact SQL. I tried it from Transact SQL editor in visual studio.

Here is the SQL transact I used:

USE TestDB;
GO
BACKUP DATABASE TestDB
TO DISK = 'E:\aa.Bak'
   WITH FORMAT,
      MEDIANAME = 'Z_SQLServerBackups',
      NAME = 'Full Backup of AdventureWorks2012';
GO

I want to execute this in C#.

Also whenever I click the backup button, I want to create a backup file and replace any existing backup file. Does the 'FORMAT' parameter does this purpose?
Can I set the parameter TO DISK (location where the backup file is created) to a location in an external hard disk or pen drive?

I can restore a databse as follows:

private void button4_restore(object sender, EventArgs e)<br/>
        {
            con.Open();<br/>
            string str = "USE master;";<br/>
            string str1= "ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;";    
            string str3="RESTORE DATABASE TestDB FROM DISK = 'E:\\aa.Bak' ";<br/>
            SqlCommand cmd = new SqlCommand(str, con);<br/>
            SqlCommand cmd1 = new SqlCommand(str1, con);<br/>
            SqlCommand cmd3 = new SqlCommand(str3, con);<br/>
            cmd.ExecuteNonQuery();<br/>
            cmd1.ExecuteNonQuery();<br/>
            cmd3.ExecuteNonQuery();<br/>
            MessageBox.Show("RECOVERED");<br/>
            con.Close();   
        }

When I restore like this, the database is restored completely to the backup file. Is there a way by which I can append the data in .Bak file with the existing data in current database.

Answer 1

The following code backups the database 'TestDB' to the file 'E.\backupfile.Bak' if button 'button_backup' is clicked.

private void button_backup_Click(object sender, EventArgs e)
        {
            //con is the connection string
            con.Open();
            string str = "USE TestDB;";
            string str1="BACKUP DATABASE TestDB TO DISK = 'E:\\backupfile.Bak' WITH FORMAT,MEDIANAME = 'Z_SQLServerBackups',NAME = 'Full Backup of Testdb';";
            SqlCommand cmd1 = new SqlCommand(str, con);
            SqlCommand cmd2 = new SqlCommand(str1, con);
            cmd1.ExecuteNonQuery();
            cmd2.ExecuteNonQuery();
            MessageBox.Show("success");
            con.Close();
        }