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!

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

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

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);
            }
}
Comments
best solution on the thread

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 :)

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

Edited 7 Years Ago by __avd: Use [code] tags to wrap your source code.

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(":)");

This article has been dead for over six months. Start a new discussion instead.