c# 备份还原sql数据库

最新推荐文章于 2022-07-11 08:41:54 发布

曹振华

最新推荐文章于 2022-07-11 08:41:54 发布

阅读量1.3k

点赞数

分类专栏： c# 文章标签： c# 备份 sql数据库

本文链接：https://blog.csdn.net/chadcao/article/details/5989848

版权

c# 专栏收录该内容

82 篇文章 0 订阅

订阅专栏

1) 配置文件

<!--数据库服务器名-->
<add key="server" value="DG-CAOZHENHUA/S2005"/>
<!--数据库名-->
<add key="databases" value="school1;school2;"/>
<!--备份数据库所在文件夹路径-->
<add key="databaseDirPath" value="C:/"/>

获取配置文件代码

private string databaseDirPath = ConfigurationManager.AppSettings["databaseDirPath"].ToString();//数据库所在文件夹路径
private string server = ConfigurationManager.AppSettings["server"].ToString();//数据库服务器名
private string databases = ConfigurationManager.AppSettings["databases"].ToString();//数据库名

2) 备份数据库

/// <summary>
        /// 备份数据库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnBackupDB_Click(object sender, EventArgs e)
        {
            string[] dbs = databases.Split(';');
            foreach (string db in dbs)
            {
                if (db != "")
                {
                    try
                    {
                        string backupDBPath = databaseDirPath + db+".bak";//备份数据库路径
                        if (DelExistFile(backupDBPath))//删除已存在备份文件成功
                        {
                            string connection = string.Format("SERVER='{0}';DATABASE=master; TRUSTED_CONNECTION=true;", server);//连接数据库语句
                            string sql = string.Format("Backup database {0} to disk = '{1}'", db, backupDBPath); //备份数据库语句
                            SqlConnection conn = new SqlConnection(connection);
                            conn.Open();
                            SqlCommand command = new SqlCommand(sql, conn);
                            command.ExecuteNonQuery();
                            Log.WriteLog(db + " 备份成功!");
                        }
                    }
                    catch (Exception ex)
                    {
                        Log.WriteLog(ex.ToString());
                    }
                }
            }
        }
 /// <summary>
        /// 判断文件是否存在如果存在删除
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        private bool DelExistFile(string filePath)
        {
            bool flag = false;
            try
            {
                // 如果备份文件存在就删除
                if (File.Exists(filePath))
                {
                    File.Delete(filePath);
                    Log.WriteLog(filePath + "删除成功!");
                }
                flag = true;
            }
            catch (Exception ex)
            {
                flag = false;
            }
            return flag;
        }
    }

3) 还原数据库

/// <summary>
        /// 还原数据库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnRestoreDB_Click(object sender, EventArgs e)
        {
            string[] dbs = databases.Split(';');
            foreach (string db in dbs)
            {
                string backupDBPath = databaseDirPath + db + ".bak";//备份数据库路径
                if (db != "")
                {
                    try
                    {
                        string connection = string.Format("SERVER='{0}';DATABASE=master; TRUSTED_CONNECTION=true;", server);//连接数据库语句
                        string sql = string.Format("restore database {0} from disk ='{1}' with replace", db, backupDBPath);
                        SqlConnection conn = new SqlConnection(connection);
                        conn.Open();
                        SqlCommand command = new SqlCommand(sql, conn);
                        command.ExecuteNonQuery();
                        Log.WriteLog(db + " 还原成功!");
                    }
                    catch (Exception ex)
                    {
                        Log.WriteLog(ex.ToString());
                    }
                }
            }
        }