CODEWITHSUNDEEP
c#sqlsql-serverdatabase-backups
Muhammed Salah
Muhammed Salah

Reputation: 271

Backup SQL Server database by C# statement

I want to create a backup for the database I worked on my application by C# statement.

This is my code:

SqlConnection con = new SqlConnection(Connection.GetConnection());
SqlCommand command = new SqlCommand();

command.CommandText = "backup database [Pharmacy Database]to disk ="+"'"+path +"'";
command.CommandType = CommandType.Text;
command.Connection = con;

con.Open();
command.ExecuteNonQuery();
con.Close();

And gives me an error:

Cannot open backup device 'C:/Users/Abo Sala7/Desktop'.Operating system error 5 (failed to retrieve text for this error. Reason:15105).
BACKUP DATABASE is terminating abnormally.

Upvotes: 2

Views: 13981

Answers (4)

IR.Programmer
IR.Programmer

Reputation: 147

    internal void CreateDbBackup()  
{  
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConStr"].ConnectionString))  
         {  
            SqlCommand cmd = con.CreateCommand();  
            cmd.CommandText = string.Format(@"BACKUP DATABASE [MyDatabase] TO  DISK = N'{0}' WITH  INIT ,  NOUNLOAD ,  NOSKIP ,  STATS = 10,  NOFORMAT", UtilityClassGeneral.DbBackupPath);  
            con.Open();  
            cmd.ExecuteNonQuery(); 
        }  
    }  

    internal void RestoreDbFromBackup()  
    {  
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConStr"].ConnectionString))  
        {  
            SqlCommand cmd = con.CreateCommand();  

            con.Open();  

            // Make sure to get exclusive access to DB to avoid any errors  
            cmd.CommandText = "USE MASTER ALTER DATABASE [MyDatabase] SET SINGLE_USER With ROLLBACK IMMEDIATE";  
            cmd.ExecuteNonQuery();  

            cmd.CommandText = string.Format(@"RESTORE DATABASE [MyDatabase] FROM  DISK = N'{0}' WITH  FILE = 1,  NOUNLOAD ,  STATS = 10,  RECOVERY ,  REPLACE", UtilityClassGeneral.DbBackupPath);  
            cmd.ExecuteNonQuery();  
        }  
}

Upvotes: 0

Rahul Shinde
Rahul Shinde

Reputation: 1662

Here is a procedure is use for back up in C#.Hope it helps

    public void BackupDatabase 
   (string BackUpLocation, string BackUpFileName, string DatabaseName, string                       ServerName )
   {

    DatabaseName = "[" + DatabaseName + "]";

    string fileUNQ = DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + "_" + DateTime.Now.Year.ToString() +"_"+ DateTime.Now.Hour.ToString()+ DateTime.Now .Minute .ToString () + "_" + DateTime .Now .Second .ToString () ;

    BackUpFileName = BackUpFileName + fileUNQ + ".bak";
    string SQLBackUp = @"BACKUP DATABASE " + DatabaseName + " TO DISK = N'" + BackUpLocation + @"\" + BackUpFileName + @"'";

    string svr = "Server=" + ServerName + ";Database=master;Integrated Security=True";

    SqlConnection cnBk = new SqlConnection(svr);
    SqlCommand cmdBkUp = new SqlCommand(SQLBackUp, cnBk);

    try
    {
        cnBk.Open();
        cmdBkUp.ExecuteNonQuery();
        Label1.Text = "Done";
        Label2.Text = SQLBackUp + " ######## Server name " + ServerName + " Database " + DatabaseName + " successfully backed up to " + BackUpLocation + @"\" + BackUpFileName + "\n Back Up Date : " + DateTime.Now.ToString();
    }

    catch (Exception ex)
    {
        Label1.Text = ex.ToString();
        Label2.Text = SQLBackUp + " ######## Server name " + ServerName + " Database " + DatabaseName + " successfully backed up to " + BackUpLocation + @"\" + BackUpFileName + "\n Back Up Date : " + DateTime.Now.ToString();
    }

    finally
    {
        if (cnBk.State == ConnectionState.Open)
        {

            cnBk .Close(); 
        } 
  } 
}

Upvotes: 0

Kurubaran
Kurubaran

Reputation: 8902

I have been using the code below for back up, try this.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;


        /// <summary>
        /// Backups the data base.
        /// </summary>
        /// <param name="fileName">Name of the file.</param>
        /// <returns></returns>
        public bool BackupDataBase(string fileName)
        {
            if (string.IsNullOrEmpty(fileName))
                return false;
            bool isDatabackedUp = true;
            try
            {

                Backup sqlBackup = new Backup();

                sqlBackup.Action = BackupActionType.Database;
                sqlBackup.BackupSetDescription = "ArchiveDataBase:" +
                                                 DateTime.Now.ToShortDateString();

                sqlBackup.BackupSetName = "Archive";


                BackupDeviceItem deviceItem = new BackupDeviceItem(fileName, DeviceType.File);
                ServerConnection connection = new ServerConnection(this.BackupConnection);
                DataConnection dataConnection = new DataConnection();

                Server sqlServer = new Server(dataConnection.ServerName);
                Database db = sqlServer.Databases[dataConnection.DataBaseName];

                sqlBackup.Database = dataConnection.DataBaseName;
                sqlBackup.Initialize = true;
                sqlBackup.Checksum = true;
                sqlBackup.ContinueAfterError = true;

                sqlBackup.Devices.Add(deviceItem);
                sqlBackup.Incremental = false;

                sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
                sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;

                sqlBackup.FormatMedia = false;

                sqlBackup.SqlBackup(sqlServer);

                return isDatabackedUp;

            }
            catch (Exception)
            {
                return false;

            }

        }


    private SqlConnection BackupConnection
    {
        get
        {
            string backupConnectionString = string.Empty;
            ConnectionStringSettings settings =
                ConfigurationManager.ConnectionStrings["LibrarySystemBackUpConnection"];
            backupConnectionString = settings.ConnectionString;

            SqlConnection backupDatabaseConnection = new SqlConnection(backupConnectionString);
            return backupDatabaseConnection;
        }
    }

Upvotes: 0

Cadburry
Cadburry

Reputation: 1864

Maybe the Problem is that your ServiceUser of the SQL-Service does not have the permission to write into the defined folder - The service is perfoming the backup - so this user must have the requiered permissions on the destination folder. (error 5 == Accessdenied)

Upvotes: 2

Related Questions