ADO.NET 备份还原数据库

1.首先在APP.config 里添加配置

<pre name="code" class="html"><appSettings>
    <!--需要备份的数据库名字-->
    <add key="_Bak_DatabaseName" value="DemoTestDB"/>
    <!--将数据库备份到的数据库名称-->
    <add key="_Bak_ToBakName" value="DemoTestDB备份库名字"/>
    <!--数据库备份地址-->
    <add key="_Bak_Path" value="E:/DB/"/>
    
    <!--数据库备份间隔时间（DAY：每天备份；MONTH：每月备份）-->
    <add key="_Bak_Time" value="Day"/>

    
    <!--需要还原的数据库名-->
    <add key="_Restore_DatabaseName" value="DemoTestDB"/>
    <!--还原数据库地址-->
    <add key="_Restore_FromDisk" value="E:/DB/DemoTestDB_2015-6-16.bak"/>
  </appSettings>

2.下面就是代码啦

这是获取配置文件里的信息

//连接数据库语句
        private readonly string _connString = ConfigurationManager.AppSettings["_Connection"];
        //需要备份数据库名字
        private readonly string _databaseName = ConfigurationManager.AppSettings["_Bak_DatabaseName"];
        //备份地址
        private readonly string _bakPath = ConfigurationManager.AppSettings["_Bak_Path"];
        //备份库名字
        private readonly string _toBakName = ConfigurationManager.AppSettings["_Bak_ToBakName"];
        //备份间隔时间
        private readonly string _bakTimeType = ConfigurationManager.AppSettings["_Bak_Time"];
        //需要还原的表名
        private readonly string _databaseNameRestore = ConfigurationManager.AppSettings["_Restore_DatabaseName"];
        //还原数据库地址
        private readonly string _fromDisk = ConfigurationManager.AppSettings["_Restore_FromDisk"];

这是备份代码：

/// <summary>
        /// 数据库备份
        /// </summary>
        /// <param name="dt">时间（备份文件名）</param>
        /// <param name="bakFileName">备份数据库文件名</param>
        public void DatabaseBak(DateTime dt, string bakFileName)
        {
            var connection = new SqlConnection(_connString);
            var sqlStr = @"USE [master];BACKUP DATABASE " + _databaseName + " TO DISK = '" + _bakPath + bakFileName + "' WITH INIT;";
            var cmd = new SqlCommand(sqlStr, connection);
            try
            {
                connection.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
        }

这是还原代码：

/// <summary>
        /// 数据库还原
        /// </summary>
        public void DatabaseRestore()
        {
            var connection = new SqlConnection(_connString);
            connection.Open();
            //杀进程
            var sqlKillProcess = @"select spid from sysprocesses,sysdatabases where sysprocesses.dbid=sysdatabases.dbid and sysdatabases.Name='" + _databaseNameRestore + "'";
            var cmdKillDatabaseProcess = new SqlCommand(sqlKillProcess, connection);
            SqlDataReader dr;
            dr = cmdKillDatabaseProcess.ExecuteReader();
            var list = new ArrayList();
            while (dr.Read())
            {
                list.Add(dr.GetInt16(0));
            }
            dr.Close();
            for (int i = 0; i < list.Count; i++)
            {
                cmdKillDatabaseProcess = new SqlCommand(string.Format(@"KILL {0}", list[i]), connection);
                cmdKillDatabaseProcess.ExecuteNonQuery();
            }

            //开始还原
            var sqlStr = @"use [master];RESTORE DATABASE " + _databaseNameRestore + " FROM DISK='" + _fromDisk + "' WITH REPLACE";
            var cmd = new SqlCommand(sqlStr, connection);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
        }

这是判断备份文件是否存在的代码：

备注：首先判断备份执行的间隔时间，即 每天备份一次，还是每月备份一次

public void BakBySetTime(DateTime dt)
        {
            var fileName = _toBakName;
            if (_bakTimeType.ToUpper() == "DAY")
            {
                fileName = fileName + "_" + dt.Year + "-" + dt.Month + "-" + dt.Day + ".bak";
            }
            else if (_bakTimeType.ToUpper() == "MONTH")
            {
                fileName = fileName + "_" + dt.Year + "-" + dt.Month + ".bak";
            }

            //判断文件路径是否存在
            if (!Directory.Exists(_bakPath))
            {
                Directory.CreateDirectory(_bakPath); //不存在就创建目录 
            }

            //判断文件是否存在 
            if (!File.Exists(_bakPath + fileName))
            {
                //不存在，则备份
                DatabaseBak(dt, fileName);
            }
        }

如果有好的建议或意见，欢迎吐槽