mvc項(xiàng)目中實(shí)現(xiàn)備份數(shù)據(jù)庫(kù)（sqlserver2005）

最近在項(xiàng)目中遇到一個(gè)問(wèn)題，本人菜鳥(niǎo)不會(huì)解決，在此歸納整理一下，方便以后看看，順便也許能幫上一些受此困擾的菜鳥(niǎo)們！

功能要求：mvc項(xiàng)目，實(shí)現(xiàn)數(shù)據(jù)庫(kù)備份（bak文件）

實(shí)現(xiàn)步驟：數(shù)據(jù)庫(kù)備份

方法：1、新建類(lèi)DatabaseMaintenance

????

//////?數(shù)據(jù)庫(kù)維護(hù)
????///public?class?DatabaseMaintenance
????{
????????//////?備份數(shù)據(jù)庫(kù)
????????//////備份文件的路徑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();
????????????}
????????}

????????//////?還原數(shù)據(jù)庫(kù)?database
????????//////要還原的數(shù)據(jù)庫(kù)文件路徑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?IMMEDIATEn"?+
????????????????????"BEGIN?TRYn"?+
????????????????????????"RESTORE?DATABASE?[{0}]?FROM?DISK?=?'{1}'?WITH?REPLACEn"?+
????????????????????"END?TRYn"?+
????????????????????"BEGIN?CATCHn"?+
????????????????????????"SET?@ErrorMessage?=?ERROR_MESSAGE()n"?+
????????????????????"END?CATCHn"?+
????????????????????"ALTER?DATABASE?[{0}]?SET?MULTI_USER?WITH?ROLLBACK?IMMEDIATEn"?+
????????????????????"IF?(@ErrorMessage?is?not?NULL)n"?+
????????????????????"BEGINn"?+
????????????????????????"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、在控制器里調(diào)用方法

?

?//備份數(shù)據(jù)庫(kù)
????????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、刪除已經(jīng)備份的數(shù)據(jù)庫(kù)bak文件

?

?

?//刪除數(shù)據(jù)庫(kù)備份文件
????????public?string?DelDataBase(string?id)
????????{
????????????try
????????????{
????????????????string?filepath=Server.MapPath("~/Data/"+id);
????????????????System.IO.File.Delete(filepath);
????????????????return?"刪除成功";
????????????}
????????????catch?{
????????????????return?"刪除失敗";
????????????}
????????}

?