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

最近在项目中遇到一个问题，本人菜鸟不会解决，在此归纳整理一下，方便以后看看，顺便也许能帮上一些受此困扰的菜鸟们！

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

实现步骤：<a id="backupDB" href="javascript:" >数据库备份</a>

方法：1、新建类DatabaseMaintenance

    

/// <summary>
    /// 数据库维护
    /// </summary>
    public class DatabaseMaintenance
    {
        /// <summary>
        /// 备份数据库
        /// </summary>
        /// <param name="fileName">备份文件的路径</param>
        public static void Backup(string fileName)
        {
            //TODO SQL Server only now
            string sqlConnectionString = ConfigurationManager.ConnectionStrings["HelpStoreContext"].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();
            }
        }

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

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

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

 

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 "备份失败";
            }
        }

 

3、删除已经备份的数据库bak文件

 

 

 //删除数据库备份文件
        public string DelDataBase(string id)
        {
            try
            {
                string filepath=Server.MapPath("~/Data/"+id);
                System.IO.File.Delete(filepath);
                return "删除成功";
            }
            catch {
                return "删除失败";
            }
        }