使用C#语句备份SQL Server数据库

本文关键字：Server 数据库 SQL 备份 语句 使用 | 更新日期: 2023-09-27 18:22:00

我想通过C#语句为我在应用程序上使用的数据库创建一个备份。

这是我的代码：

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

给了我一个错误：

无法打开备份设备"C:''Users/Abo-Sala7/Desktop"。操作系统错误5（无法检索此错误的文本。原因：15105）
BACKUP DATABASE正在异常终止。

使用C#语句备份SQL Server数据库

也许问题是您的SQL服务的ServiceUser没有权限写入定义的文件夹-该服务正在执行备份-因此该用户必须对目标文件夹具有所需的权限。（错误5==拒绝访问）

我一直在使用下面的代码进行备份，试试这个。

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

这里有一个在C#中用于备份的过程。希望它能帮助

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

    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();  
        }  
}
相关文章：