C#實現(xiàn)SQL SERVER數(shù)據(jù)庫備份的兩種方法比較
當(dāng)前位置：點晴教程→知識管理交流
→『 技術(shù)文檔交流 』
先把代碼貼上吧 using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; using System.Data; using System.Windows.Forms; namespace DBAdmin { public class DbBackUpAndRestore { /// <summary> /// 服務(wù)器 /// </summary> private string server = ""; public string Server { get { return this.server; } set { this.server = value; } } /// <summary> /// 登錄名 /// </summary> private string uid = ""; public string UID { get { return this.uid; } set { this.uid = value; } } /// <summary> /// 登錄密碼 /// </summary> private string pwd = ""; public string PWD { get { return this.pwd; } set { this.pwd = value; } } /// <summary> /// 要操作的數(shù)據(jù)庫 /// </summary> private string database = ""; public string Database { get { return this.database; } set { this.database = value; } } /// <summary> /// 數(shù)據(jù)庫連接字符串 /// </summary> private string conn = ""; /// <summary> /// 備份路經(jīng) /// </summary> private string backPath = ""; public string BackPath { get { return this.backPath; } set { this.backPath = value; } } /// <summary> /// 還原文件路經(jīng) /// </summary> private string restoreFile = ""; public string RestoreFile { get { return this.restoreFile; } set { this.restoreFile = value; } } private ProgressBar bar; public ProgressBar Bar { get { return bar; } set { bar = value; } } /// <summary> /// DbBackUpAndRestore類的構(gòu)造函數(shù) /// </summary> public DbBackUpAndRestore() { } /// <summary> /// 切割字符串 /// </summary> /// <param name="str"></param> /// <param name="bg"></param> /// <param name="ed"></param> /// <returns></returns> public string StringCut(string str, string bg, string ed) { string sub; sub = str.Substring(str.IndexOf(bg) + bg.Length); sub = sub.Substring(0, sub.IndexOf(";")); return sub; } /// <summary> /// 構(gòu)造文件名 /// </summary> /// <returns>文件名</returns> private void CreatePath() { string CurrTime = System.DateTime.Now.ToString(); CurrTime = CurrTime.Replace("-", ""); CurrTime = CurrTime.Replace(":", ""); CurrTime = CurrTime.Replace(" ", ""); CurrTime = CurrTime.Substring(0, 12); backPath += "http://_db_" + CurrTime + ".BAK"; } private void Step(string message, int percent) { Bar.Value = percent; } /// <summary> /// 數(shù)據(jù)庫備份 /// </summary> /// <returns>備份是否成功</returns> public bool DbBackup() { CreatePath(); SQLDMO.Backup oBackup = new SQLDMO.BackupClass(); SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass(); try { oSQLServer.LoginSecure = false; oSQLServer.Connect(server, uid, pwd); oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database; SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step); oBackup.PercentComplete += pceh; oBackup.Database = database; oBackup.Files = backPath; oBackup.BackupSetName = database; oBackup.BackupSetDescription = "數(shù)據(jù)庫備份"; oBackup.Initialize = true; oBackup.SQLBackup(oSQLServer); return true; } catch (Exception ex) { return false; throw ex; } finally { oSQLServer.DisConnect(); } } /// <summary> /// 數(shù)據(jù)庫恢復(fù) /// </summary> public string DbRestore() { if (exepro() != true) { return "操作失敗"; } else { SQLDMO.Restore oRestore = new SQLDMO.RestoreClass(); SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass(); try { exepro(); oSQLServer.LoginSecure = false; oSQLServer.Connect(server, uid, pwd); oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database; SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step); oRestore.PercentComplete += pceh; oRestore.Database = database; ///自行修改 oRestore.Files = restoreFile; oRestore.FileNumber = 1; oRestore.ReplaceDatabase = true; oRestore.SQLRestore(oSQLServer); return "數(shù)據(jù)庫恢復(fù)成功"; } catch (Exception e) { return "恢復(fù)數(shù)據(jù)庫失敗，原因:" + e.Message; throw e; } finally { oSQLServer.DisConnect(); } } } /// <summary> /// 殺死當(dāng)前庫的所有進(jìn)程 /// </summary> /// <returns></returns> private bool exepro() { bool success = true; SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass(); try { svr.Connect(server, uid, pwd); //取得所有的進(jìn)程列表 SQLDMO.QueryResults qr = svr.EnumProcesses(-1); int iColPIDNum = -1; int iColDbName = -1; //找到和要恢復(fù)數(shù)據(jù)庫相關(guān)的進(jìn)程 for (int i = 1; i <= qr.Columns; i++) { string strName = qr.get_ColumnName(i); if (strName.ToUpper().Trim() == "SPID") { iColPIDNum = i; } else if (strName.ToUpper().Trim() == "DBNAME") { iColDbName = i; } if (iColPIDNum != -1 && iColDbName != -1) break; } //將相關(guān)進(jìn)程關(guān)閉 for (int i = 1; i <= qr.Rows; i++) { int lPID = qr.GetColumnLong(i, iColPIDNum); string strDBName = qr.GetColumnString(i, iColDbName); if (strDBName.ToUpper() == database) svr.KillProcess(lPID); } } catch (Exception ex) { success = false; } return success; } public bool Operate(bool isBackup) { //備份：use master;backup database @name to disk=@path; //恢復(fù)：use master;restore database @name from disk=@path; SqlConnection connection = new SqlConnection("Data Source=" + server + ";initial catalog=" + database + ";user id=" + uid + ";password=" + pwd + ";"); if (!restoreFile.EndsWith(".bak")) { restoreFile += ".bak"; } if (isBackup)//備份數(shù)據(jù)庫 { SqlCommand command = new SqlCommand("use master;backup database @name to disk=@path;", connection); connection.Open(); command.Parameters.AddWithValue("@name", Database); command.Parameters.AddWithValue("@path", restoreFile); command.ExecuteNonQuery(); connection.Close(); } else//恢復(fù)數(shù)據(jù)庫 { SqlCommand command = new SqlCommand("use master;restore database @name from disk=@path;", connection); connection.Open(); command.Parameters.AddWithValue("@name", Database); command.Parameters.AddWithValue("@path", restoreFile); command.ExecuteNonQuery(); connection.Close(); } return true; } } } 第一種方法是利用庫SQLDMO來進(jìn)行數(shù)據(jù)庫的備份和恢復(fù)。這種方法在恢復(fù)時，原數(shù)據(jù)不能正在使用當(dāng)中，可以先把原數(shù)據(jù)庫刪除，適用于數(shù)據(jù)庫誤刪除或者sql server數(shù)據(jù)庫軟件重裝的情況下進(jìn)行.當(dāng)然這種情況下，發(fā)布時，必須把庫SQLDMO打包。
第二種方式是通過調(diào)用sql指令來完成數(shù)據(jù)庫的備份和還原。這種方法在恢復(fù)時，原數(shù)據(jù)必須存在，恢復(fù)時覆蓋掉原數(shù)據(jù)庫的數(shù)據(jù)。
備份的情況當(dāng)然是數(shù)據(jù)庫必須存在而且已經(jīng)附加到數(shù)據(jù)庫管理器中。
可以根據(jù)需要采用這兩種方式 該文章在 2018/1/30 23:58:43 編輯過 |
關(guān)鍵字查詢
相關(guān)文章
正在查詢... 
|
400 186 1886
