mvc项目中实现备份数据库（sqlserver2012）

功能要求：mvc项目，实现数据库备份（bak文件）

方法：

1、新建类DatabaseMaintenance

  public class DatabaseMaintenance
   {

       #region 备份数据库
       /// <summary>  
        /// 备份数据库  
        /// </summary>  
        /// <param name="fileName">备份文件的路径</param>  
        public static void Backup(string fileName)
        {
            //TODO SQL Server only now  
            string sqlConnectionString = ConfigurationManager.ConnectionStrings["connStr"].ToString();
            using (SqlConnection conn = new SqlConnection(sqlConnectionString))
            {
                string dbName = new SqlConnectionStringBuilder(sqlConnectionString).InitialCatalog;
                ;
                string commandText = string.Format(
                    "BACKUP DATABASE [{0}] TO DISK = '{1}' WITH FORMAT",
                    dbName,
                    fileName);

                DbCommand dbCommand = new SqlCommand(commandText, conn);
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                dbCommand.ExecuteNonQuery();
            }
        }
       #endregion
       

        #region 还原数据库
        /// <summary>  
        /// 还原数据库 database  
        /// </summary>  
        /// <param name="fileName">要还原的数据库文件路径</param>  
        public static void RestoreBackup(string fileName)
        {
            string sqlConnectionString = ConfigurationManager.ConnectionStrings["connStr"].ToString();
            using (SqlConnection conn = new SqlConnection(sqlConnectionString))
            {
                string dbName = new SqlConnectionStringBuilder(sqlConnectionString).InitialCatalog;
                string commandText = string.Format(
                    "DECLARE @ErrorMessage NVARCHAR(4000)
" +
                    "USE master ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
" +
                    "BEGIN TRY
" +
                        "RESTORE DATABASE [{0}] FROM DISK = '{1}' WITH REPLACE
" +
                    "END TRY
" +
                    "BEGIN CATCH
" +
                        "SET @ErrorMessage = ERROR_MESSAGE()
" +
                    "END CATCH
" +
                    "ALTER DATABASE [{0}] SET MULTI_USER WITH ROLLBACK IMMEDIATE
" +
                    "IF (@ErrorMessage is not NULL)
" +
                    "BEGIN
" +
                        "RAISERROR (@ErrorMessage, 16, 1)
" +
                    "END",
                    dbName,
                    fileName);

                DbCommand dbCommand = new SqlCommand(commandText, conn);
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                dbCommand.ExecuteNonQuery();
            }

            //clear all pools  
            SqlConnection.ClearAllPools();
        }
        #endregion


       
    }

PS:有位网友的方法 缺少红色标注部分，结果还原的时候一直报错，经过查找资料后 解决。

2、在控制器里调用方法

//备份数据库  
       public string BackupData()  
       {  
           try  
           {  
               var dname = DateTime.Now.Ticks;  
               string filename = Server.MapPath("~/Data/" + dname + ".bak");  
               if (!System.IO.File.Exists(filename))  
               {  
                   System.IO.File.Create(filename);  
               }  
               DatabaseMaintenance.Backup(filename);  
               return "备份成功";  
           }  
           catch  
           {  
               return "备份失败";  
           }  
       } 
//删除数据库备份文件  
       public string DelDataBase(string id)  
       {  
           try  
           {  
               string filepath=Server.MapPath("~/Data/"+id);  
               System.IO.File.Delete(filepath);  
               return "删除成功";  
           }  
           catch {  
               return "删除失败";  
           }  
       }  

PS:控制器的路径根据自己的需求而定。

参考链接：点这里

在此记录，仅供日后参考。