Backup SQL Server database by C# statement

Question

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.

Perhaps missing a WITH FORMAT option? Also good to specify a full file name, not a directory. — user645280
– user645280, Commented Jul 10, 2013 at 15:11

Cadburry · Accepted Answer · 2013-07-10 15:16:54Z

2

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)

answered Jul 10, 2013 at 15:16

Cadburry

1,86410 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Muhammed Salah Over a year ago

so how can i give it the permission @Cadburry

Cadburry Over a year ago

you can grant the permission to the destinationfolder(take a look at the sql-service which user is configured) (start - run - services.msc -> SQL Server -> register "logon") Or change the SQLService user to "LocalSystem" - but be careful this gives the sql service full permission to you machine... i would prefere the first way..

Cadburry Over a year ago

Hint: if the backup of the database takes "too" long - with your code you will run into an timeout.. and the "command.ExecuteNonQuery();" will throw an exception... increase the command timeout by setting "command.CommandTimeout" to an appropriate value

Muhammed Salah Over a year ago

i do the second one because i don't know how to do it and the same problem in addition to it make another problem which is i can run the application in visual studio more than one time although the same application is already running

Cadburry Over a year ago

by the way... in your given code - when u set the CommandText "...[Pharmacy Database]to "... you have no space between the databasename and the "to" keyword...

Kurubaran · Accepted Answer · 2013-07-10 15:23:16Z

0

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

answered Jul 10, 2013 at 15:23

Kurubaran

8,9625 gold badges47 silver badges71 bronze badges

8 Comments

Muhammed Salah Over a year ago

The Code U Gave Me Have A of Errors In Header File using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Common;

Kurubaran Over a year ago

Did you add the assembly references ?

Kurubaran Over a year ago

using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Common; you have to add assembly reference to your project. Because these namespaces are available in those dlls

Kurubaran Over a year ago

In a WIN7 machine you can find those dlls under C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies

Muhammed Salah Over a year ago

so how could i to add those references

|

Rahul Shinde · Accepted Answer · 2014-06-20 12:04:44Z

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

IR.Programmer · Accepted Answer · 2014-12-13 17:07:41Z

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

Collectives™ on Stack Overflow

Backup SQL Server database by C# statement

4 Answers 4

5 Comments

8 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related