c# 备份还原sql数据库

来源：互联网发布：mac foxmail 存储位置编辑：程序博客网时间：2024/05/17 23:54

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