ADO.NET 备份还原数据库

来源：互联网发布：心灵召唤技能的db数据编辑：程序博客网时间：2024/05/16 04:46

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

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

0 0