C#实现SQL SERVER数据库备份的两种方法比较

admin

2018年1月30日 23:58 本文热度 6722

先把代码贴上吧

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
namespace DBAdmin
{
    public class DbBackUpAndRestore
    {
        /// <summary>
        /// 服务器
        /// </summary>
        private string server = "";
        public string Server
        {
            get { return this.server; }
            set { this.server = value; }
        }
        /// <summary>
        /// 登录名
        /// </summary>
        private string uid = "";
        public string UID
        {
            get { return this.uid; }
            set { this.uid = value; }
        }
        /// <summary>
        /// 登录密码
        /// </summary>
        private string pwd = "";
        public string PWD
        {
            get { return this.pwd; }
            set { this.pwd = value; }
        }
        /// <summary>
        /// 要操作的数据库
        /// </summary>
        private string database = "";
        public string Database
        {
            get { return this.database; }
            set { this.database = value; }
        }
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string conn = "";
        /// <summary>
        /// 备份路经
        /// </summary>
        private string backPath = "";
        public string BackPath
        {
            get { return this.backPath; }
            set { this.backPath = value; }
        }
        /// <summary>
        /// 还原文件路经
        /// </summary>
        private string restoreFile = "";
        public string RestoreFile
        {
            get { return this.restoreFile; }
            set { this.restoreFile = value; }
        }
        private ProgressBar bar;

        public ProgressBar Bar
        {
            get { return bar; }
            set { bar = value; }
        }
        /// <summary>
        /// DbBackUpAndRestore类的构造函数
        /// </summary>
        public DbBackUpAndRestore()
        {
        }
        /// <summary>
        /// 切割字符串
        /// </summary>
        /// <param name="str"></param>
        /// <param name="bg"></param>
        /// <param name="ed"></param>
        /// <returns></returns>
        public string StringCut(string str, string bg, string ed)
        {
            string sub;
            sub = str.Substring(str.IndexOf(bg) + bg.Length);
            sub = sub.Substring(0, sub.IndexOf(";"));
            return sub;
        }
        /// <summary>
        /// 构造文件名
        /// </summary>
        /// <returns>文件名</returns>
        private void CreatePath()
        {
            string CurrTime = System.DateTime.Now.ToString();
            CurrTime = CurrTime.Replace("-", "");
            CurrTime = CurrTime.Replace(":", "");
            CurrTime = CurrTime.Replace(" ", "");
            CurrTime = CurrTime.Substring(0, 12);
            backPath += "//_db_" + CurrTime + ".BAK";
        }
        private void Step(string message, int percent)
        {
            Bar.Value = percent;
        }
        /// <summary>
        /// 数据库备份
        /// </summary>
        /// <returns>备份是否成功</returns>
        public bool DbBackup()
        {
            CreatePath();
            SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(server, uid, pwd);
                oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
                SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
                oBackup.PercentComplete += pceh;
                oBackup.Database = database;
                oBackup.Files = backPath;
                oBackup.BackupSetName = database;
                oBackup.BackupSetDescription = "数据库备份";
                oBackup.Initialize = true;
                oBackup.SQLBackup(oSQLServer);
                return true;
            }
            catch (Exception ex)
            {
                return false;
                throw ex;
            }
            finally
            {
                oSQLServer.DisConnect();
            }
        }
        /// <summary>
        /// 数据库恢复
        /// </summary>
        public string DbRestore()
        {
            if (exepro() != true)
            {
                return "操作失败";
            }
            else
            {
                SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
                SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
                try
                {
                    exepro();
                    oSQLServer.LoginSecure = false;
                    oSQLServer.Connect(server, uid, pwd);
                    oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                    SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
                    oRestore.PercentComplete += pceh;
                    oRestore.Database = database;
                    ///自行修改
                    oRestore.Files = restoreFile;
                    oRestore.FileNumber = 1;
                    oRestore.ReplaceDatabase = true;
                    oRestore.SQLRestore(oSQLServer);
                    return "数据库恢复成功";
                }
                catch (Exception e)
                {
                    return "恢复数据库失败，原因:" + e.Message;
                    throw e;
                }
                finally
                {
                    oSQLServer.DisConnect();
                }
            }
        }
        /// <summary>
        /// 杀死当前库的所有进程
        /// </summary>
        /// <returns></returns>
        private bool exepro()
        {
            bool success = true;
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
            try
            {
                svr.Connect(server, uid, pwd);
                //取得所有的进程列表
                SQLDMO.QueryResults qr = svr.EnumProcesses(-1);
                int iColPIDNum = -1;
                int iColDbName = -1;
                //找到和要恢复数据库相关的进程
                for (int i = 1; i <= qr.Columns; i++)
                {
                    string strName = qr.get_ColumnName(i);
                    if (strName.ToUpper().Trim() == "SPID")
                    {
                        iColPIDNum = i;
                    }
                    else if (strName.ToUpper().Trim() == "DBNAME")
                    {
                        iColDbName = i;
                    }
                    if (iColPIDNum != -1 && iColDbName != -1)
                        break;
                }
                //将相关进程关闭
                for (int i = 1; i <= qr.Rows; i++)
                {
                    int lPID = qr.GetColumnLong(i, iColPIDNum);
                    string strDBName = qr.GetColumnString(i, iColDbName);
                    if (strDBName.ToUpper() == database)
                        svr.KillProcess(lPID);
                }
            }
            catch (Exception ex)
            {
                success = false;
            }
            return success;
        }
        public bool Operate(bool isBackup)
        {
            //备份：use master;backup database @name to disk=@path;
            //恢复：use master;restore database @name from disk=@path;
            SqlConnection connection = new SqlConnection("Data Source=" + server + ";initial catalog=" + database + ";user id=" + uid + ";password=" + pwd + ";");
            if (!restoreFile.EndsWith(".bak"))
            {
                restoreFile += ".bak";
            }
            if (isBackup)//备份数据库
            {
                SqlCommand command = new SqlCommand("use master;backup database @name to disk=@path;", connection);
                connection.Open();
                command.Parameters.AddWithValue("@name", Database);
                command.Parameters.AddWithValue("@path", restoreFile);
                command.ExecuteNonQuery();
                connection.Close();
            }
            else//恢复数据库
            {
                SqlCommand command = new SqlCommand("use master;restore database @name from disk=@path;", connection);
                connection.Open();
                command.Parameters.AddWithValue("@name", Database);
                command.Parameters.AddWithValue("@path", restoreFile);
                command.ExecuteNonQuery();
                connection.Close();
            }
            return true;
        }
    }
}

第一种方法是利用库SQLDMO来进行数据库的备份和恢复。这种方法在恢复时，原数据不能正在使用当中，可以先把原数据库删除，适用于数据库误删除或者sql server数据库软件重装的情况下进行.当然这种情况下，发布时，必须把库SQLDMO打包。

第二种方式是通过调用sql指令来完成数据库的备份和还原。这种方法在恢复时，原数据必须存在，恢复时覆盖掉原数据库的数据。

备份的情况当然是数据库必须存在而且已经附加到数据库管理器中。

可以根据需要采用这两种方式

该文章在 2018/1/30 23:58:43 编辑过

点晴ERP是一款针对中小制造业的专业生产管理软件系统,系统成熟度和易用性得到了国内大量中小企业的青睐。

点晴PMS码头管理系统主要针对港口码头集装箱与散货日常运作、调度、堆场、车队、财务费用、相关报表等业务管理，结合码头的业务特点，围绕调度、堆场作业而开发的。集技术的先进性、管理的有效性于一体，是物流码头及其他港口类企业的高效ERP管理信息系统。

点晴WMS仓储管理系统提供了货物产品管理,销售管理,采购管理,仓储管理,仓库管理,保质期管理,货位管理,库位管理,生产管理,WMS管理系统,标签打印,条形码,二维码管理,批号管理软件。

点晴免费OA是一款软件和通用服务都免费，不限功能、不限时间、不限用户的免费OA协同办公管理系统。