Take BackUp of SQL server Database using C#

[S2009]

Hi

I am creating a windows application.

I have a button as backup.

When the user clicks this button,he/she should be able to take backup of the database.

I am a beginner. I dont have any idea about how to do this task.

Can anybody tell me how to do this?

Please help me out!

Thanks in advance!

[serkan sendur]

you should run sql script using c#, the scripts can be put in a file.

using System.Data.SqlClient;

using System.IO;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo;

 

namespace ConsoleApplication1

{

    class Program

    {

        static void Main(string[] args)

        {

            string sqlConnectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";

            FileInfo file = new FileInfo("C:\\myscript.sql"); 

            string script = file.OpenText().ReadToEnd(); 

            SqlConnection conn = new SqlConnection(sqlConnectionString); 

            Server server = new Server(new ServerConnection(conn)); 

            server.ConnectionContext.ExecuteNonQuery(script);

        }

    }

}

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/43e8bc3a-1132-453b-b950-09427e970f31

[sknake]

Here is the TSQL to back up a database, and you can use serkan's code for connection:

BACKUP DATABASE [MyDatabase] TO  DISK = 'C:\....\MyDatabase.bak' WITH  INIT ,  NOUNLOAD ,  NAME = N'MyDatabase backup',  NOSKIP ,  STATS = 10,  NOFORMAT

One important thing to note is this command is sent to the SQL Server for processing. If you tell it to save on "C:\" then it will save on the C:\ drive of the SQL Server, not your local machine. Also note that the SQL Service typically does not have full read/write access to the entire drive so you will need to create a directory for it or use the C:\Program~1\Microsoft SQL Server\ directory

[serkan sendur]

good cooperation :)

[Ramy Mahrous]

Excuse me I've another solution :) due to being fan of SMO I'll do it using it

public void (string databaseName, string filePath)
{
try
            {
                Server localServer = new Server(); //local using windows athuentication 
                Backup backupMgr = new Backup();
                backupMgr.Devices.AddDevice(filePath, DeviceType.File);
                backupMgr.Database = databaseName;
                backupMgr.Action = BackupActionType.Database;
                backupMgr.SqlBackup(localServer);
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message + " " + ex.InnerException);
            }
}

[sknake]

Excuse me I've another solution :) due to being fan of SMO I'll do it using it

public void (string databaseName, string filePath)
{
try
            {
                Server localServer = new Server(); //local using windows athuentication 
                Backup backupMgr = new Backup();
                backupMgr.Devices.AddDevice(filePath, DeviceType.File);
                backupMgr.Database = databaseName;
                backupMgr.Action = BackupActionType.Database;
                backupMgr.SqlBackup(localServer);
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message + " " + ex.InnerException);
            }
}

You win, I didn't know you could do that :)

[Ramy Mahrous]

Thanks, Scott :)

[mac1546]

The following code snippet restoresa SQL Server back up file to a local SQL Server instance. It does not require SMO which is buggy.

// Pass the path to the back up directory and the name of the .bak file to restore.
 public bool restoreDB(string filePath, string fileName)
        {
//
            bool restoreComplete = false;
            sqlConnection1.Open();
            System.Data.SqlClient.SqlCommand sqlDBrestoreCommand = new System.Data.SqlClient.SqlCommand();
            sqlDBrestoreCommand.Connection = sqlConnection1;

            try
            {
                sqlDBrestoreCommand.CommandText = "Use Master";
                sqlDBrestoreCommand.ExecuteNonQuery();
                sqlDBrestoreCommand.CommandText = "ALTER DATABASE BlackBook SET SINGLE_USER WITH ROLLBACK IMMEDIATE;";
                sqlDBrestoreCommand.ExecuteNonQuery();
                sqlDBrestoreCommand.CommandText = "RESTORE DATABASE BlackBook FROM DISK = '" + filePath + fileName + "';";
                sqlDBrestoreCommand.ExecuteNonQuery();
                sqlDBrestoreCommand.CommandText = "Use Master";
                sqlDBrestoreCommand.ExecuteNonQuery();
                sqlDBrestoreCommand.CommandText = "ALTER DATABASE BlackBook SET MULTI_USER;";
                sqlDBrestoreCommand.ExecuteNonQuery();
                restoreComplete = true;
            }
            catch (System.Exception ex)
            {
                info = ex.Message;
            }

            sqlConnection1.Close();
            return restoreComplete;
        }

[Ivan_86]

SqlConnection connect;
string con1 = @"Data Source=" + newForm.ServerName + ";Initial Catalog=" + listBox2.SelectedItem + ";Persist Security Info=True;User ID=" + newForm.ID + ";Password=" + newForm.Pass;
connect = new SqlConnection(con1);
connect.Open();
SqlCommand command;
command = new SqlCommand(@"backup database " + listBox2.SelectedItem + " to disk ='" + path + "\\" + name + "' with init,stats=10", connect);
command.ExecuteNonQuery();
connect.Close();
MessageBox.Show(":)");