c# 用SQLDMO 进行sql数据库备份及还原

只能程序与数据库同在一台机器可用，除了备份完可以下载备份文件暂外无其他办法。还有数据还原时杀掉链接，还原时提示链接失败，第二次执行正常，暂时用两次操作解决。

一、ASP.NET

  string backaway = ConfigurationManager.AppSettings["DataPath"].ToString();
        public struct sFileInfo
        {
            public string FileName;
            public DateTime FileCreateTime;
        }

        #region 获取指定目录下的某后缀名的所有文件，按时间顺序排列，将文件名及创建时间存入DataTable中
        private DataTable GetFileTable()
        {
            DirectoryInfo d = new DirectoryInfo(backaway);

            //根椐指定文件夹下的jpg文件数目，获取数组的长度 n
            int n = 0;
            foreach (FileInfo _f in d.GetFiles())
            {
                if (_f.Name.Substring(_f.Name.Length - 3, 3) == "bak")
                //注意:这里只是简单的判断名称的后三位,也就是说文件的扩展名是区分大小写
                {
                    n++;
                }
            }

            //定义数组 并对数组进行赋值
            sFileInfo[] ArrFiles = new sFileInfo[n];
            int i = 0;
            foreach (FileInfo _f in d.GetFiles())
            {
                if (_f.Name.Substring(_f.Name.Length - 3, 3) == "bak")
                {
                    ArrFiles[i].FileName = _f.Name;
                    ArrFiles[i].FileCreateTime = _f.CreationTime;
                    i++;
                }
            }

            //对数组根椐文件创建时间进行冒泡排序
            sFileInfo FileInfoTemp;
            for (int k = 0; k < n; k++)
            {
                for (int j = k + 1; j < n; j++)
                {
                    if (DateTime.Compare(ArrFiles[k].FileCreateTime, ArrFiles[j].FileCreateTime) < 0)
                    {
                        FileInfoTemp = ArrFiles[k];
                        ArrFiles[k] = ArrFiles[j];
                        ArrFiles[j] = FileInfoTemp;
                    }
                }
            }

            DataTable table = new DataTable();

            table.Columns.Add("data_path");

            table.Columns.Add("data_date");
            DataRow row;
            for (int j = 0; j < ArrFiles.Length; j++)
            {
                row = table.NewRow();
                row["data_path"] = ArrFiles[j].FileName;
                row["data_date"] = ArrFiles[j].FileCreateTime.ToString("yyyy-MM-dd HH:mm:ss");

                table.Rows.Add(row);

            }
            return table;
        }
        #endregion

       #region 数据备份
        private void DataBackups()
        {
            SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                oSQLServer.LoginSecure = false;
                //下面设置登录sql服务器的ip,登录名,登录密码

                string[] connStr = ConfigurationManager.AppSettings["ConnectionString"].ToString().Split(';');
                string servername = connStr[0].Substring(7, connStr[0].ToString().Length - 7);
                string username = connStr[1].Substring(4, connStr[1].ToString().Length - 4);
                string userpass = connStr[2].Substring(4, connStr[2].ToString().Length - 4);

                oSQLServer.Connect(servername, username, userpass);
                oBackup.Action = 0;

                //数据库名称:
                oBackup.Database = "HotMeter";//PrisonCars
                //备份的路径
                string name = "HotMeter" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".bak";
                string filename = @backaway + name;
                oBackup.Files = filename;
                //备份的文件名
                oBackup.BackupSetName = "HotMeter.bak";
                oBackup.BackupSetDescription = "数据库备份";
                oBackup.Initialize = true;
                oBackup.SQLBackup(oSQLServer);
                HotMeter.Web.LogCommon.AddLog("创建数据库备份-" + name);
                Response.Write("<script>alert('备份数据库成功！备份文件为:" + name + "');window.location.href=window.location.href</script>");
            }
            catch
            {
                //MessageBox.Show("备份失败！", "提示");
            }
            finally
            {
                oSQLServer.DisConnect();
            }
        }
        #endregion

     #region 数据还原
        private void DataRestore(string file)
        {
            for (int i = 0; i < 2; i++)//执行两次，解决杀掉进程后，主机强迫关闭连接问题。
            {
                //string dbaway = ConfigurationManager.AppSettings["DataPath"].ToString();
                string dbaway = file;
                SQLDMO.Restore restore = new SQLDMO.RestoreClass();
                SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();
                string[] connStr = ConfigurationManager.AppSettings["ConnectionString"].ToString().Split(';');
                string servername = connStr[0].Substring(7, connStr[0].ToString().Length - 7);
                string username = connStr[1].Substring(4, connStr[1].ToString().Length - 4);
                string userpass = connStr[2].Substring(4, connStr[2].ToString().Length - 4);
                server.Connect(servername, username, userpass);

                //KILL DataBase Process
                SqlConnection myCon = new SqlConnection("server=" + servername + ";uid=" + username + ";pwd=" + userpass + ";database=master");
                myCon.Open();//k
                SqlCommand cmd = new SqlCommand("use master Select spid FROM sysprocesses ,sysdatabases Where sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='HotMeter'", myCon);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    server.KillProcess(Convert.ToInt32(dr[0].ToString()));
                }
                dr.Close();
                myCon.Close();//g

                try
                {
                    restore.Action = 0;

                    restore.Database = "HotMeter";
                    restore.Files = @dbaway;
                    restore.ReplaceDatabase = true;
                    if (myCon.State != ConnectionState.Open)
                    {
                        myCon.Open();
                    }
                    restore.SQLRestore(server);
                    if (i == 1)
                    {
                        Response.Write("<script>alert('还原数据库成功！还原文件为:" + file + "');window.location.href=window.location.href</script>");
                    }
                }
                catch (Exception ex)
                {
                    //MessageBox.Show(ex.Message);
                }
                finally
                {
                    server.DisConnect();
                }
            }
        }
        #endregion

      #region 备份删除
        protected void btnDelete_Click(object sender, EventArgs e)
        {
            string idlist = GetSelIDlist();
            if (idlist.Trim().Length == 0)
                return;
            string[] s = idlist.Split(',');
            for (int i = 0; i < s.Length; i++)
            {
                string filename = s[i].ToString().Replace("'", "");
                string path = backaway + filename;

                if (File.Exists(@path))
                {
                    File.Delete(@path);
                    HotMeter.Web.LogCommon.AddLog("删除数据库备份文件-" + filename);
                }
            }

            Response.Write("<script>alert('删除成功!');window.location.href=window.location.href</script>");
        }
        #endregion

 

 

二、Winform

namespace PrisonCarsManage
{
    public partial class BackUpAndRestore : Form
    {
        public BackUpAndRestore()
        {
            InitializeComponent();
        }

        private void Step2(string message, int percent)
        {//还原进度条
            progressBar2.Value = percent;
            while (progressBar2.Value < progressBar2.Maximum)
            {
                progressBar2.PerformStep();
            }
        }

        private void Step(string message, int percent)
        {//备份进度条
            progressBar1.Value = percent;
            while (progressBar1.Value < progressBar2.Maximum)
            {
                progressBar1.PerformStep();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                if (!File.Exists(saveFileDialog1.FileName.ToString()))
                {
                    textBox1.Text = saveFileDialog1.FileName.ToString();
                }
                else
                {
                    MessageBox.Show("请重新命名！");
                }
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                textBox2.Text = openFileDialog1.FileName.ToString();
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {

            string backaway = textBox1.Text.Trim();

            SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                oSQLServer.LoginSecure = false;
                //下面设置登录sql服务器的ip,登录名,登录密码

                string[] connStr = DAL.DbHelperSQL.connectionString.ToString().Split(';');
                string servername = connStr[0].Substring(7, connStr[0].ToString().Length - 7);
                string username = connStr[1].Substring(4, connStr[1].ToString().Length - 4);
                string userpass = connStr[2].Substring(4, connStr[2].ToString().Length - 4);

                oSQLServer.Connect(servername, username, userpass);
                oBackup.Action = 0;
                //下面两句是显示进度条的状态
                SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
                oBackup.PercentComplete += pceh;
                //数据库名称:
                oBackup.Database = "PrisonCars";//PrisonCars
                //备份的路径
                oBackup.Files = @backaway;
                //备份的文件名
                oBackup.BackupSetName = "PrisonCars";
                oBackup.BackupSetDescription = "数据库备份";
                oBackup.Initialize = true;
                oBackup.SQLBackup(oSQLServer);
                Common.SysLog.AddSysLog(Common.CurrentUser.UserId, Common.CurrentUser.UserName, "备份数据库。");
                MessageBox.Show("备份成功！", "提示");
            }
            catch
            {
                MessageBox.Show("备份失败！", "提示");
            }
            finally
            {
                oSQLServer.DisConnect();
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {

            for (int i = 0; i < 2; i++)//执行两次，解决杀掉进程后，主机强迫关闭连接问题。
            {

                string dbaway = textBox2.Text.Trim();
                SQLDMO.Restore restore = new SQLDMO.RestoreClass();
                SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();
                string[] connStr = DAL.DbHelperSQL.connectionString.ToString().Split(';');
                string servername = connStr[0].Substring(7, connStr[0].ToString().Length - 7);
                string username = connStr[1].Substring(4, connStr[1].ToString().Length - 4);
                string userpass = connStr[2].Substring(4, connStr[2].ToString().Length - 4);
                server.Connect(servername, username, userpass);

                //KILL DataBase Process
                SqlConnection myCon = new SqlConnection("server=" + servername + ";uid=" + username + ";pwd=" + userpass + ";database=master");
                myCon.Open();//k
                SqlCommand cmd = new SqlCommand("use master Select spid FROM sysprocesses ,sysdatabases Where sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='PrisonCars'", myCon);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    server.KillProcess(Convert.ToInt32(dr[0].ToString()));
                }
                dr.Close();
                myCon.Close();//g

                try
                {
                    restore.Action = 0;
                    SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step2);
                    restore.PercentComplete += pceh;
                    restore.Database = "PrisonCars";
                    restore.Files = @dbaway;
                    restore.ReplaceDatabase = true;
                    if (myCon.State != ConnectionState.Open)
                    {
                        myCon.Open();
                    }
                    restore.SQLRestore(server);
                    Common.SysLog.AddSysLog(Common.CurrentUser.UserId, Common.CurrentUser.UserName, "恢复数据库。");
                    MessageBox.Show("数据库恢复成功！");
                }
                catch (Exception ex)
                {
                    //MessageBox.Show(ex.Message);
                }
                finally
                {
                    server.DisConnect();
                }
            }
        }
    }
}