熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> .NET編程 >> 正文

用asp.net還原與恢復sqlserver數據庫

2013-11-13 09:55:33  來源: .NET編程 

  上次做了個項目涉及到數據庫的還原和恢復到網上找了一下是利用SQLDMO實現的只要添加SQLDMO引用就好了然後利用下邊的類的方法就可以實現了

  我把原作者的類擴充了一下可以自動識別nfig裡 的數據庫連接字符串可以通過變量設置還原恢復的信息

  需要注意的時還原還原的時候問題最大了有別的用戶使用數據庫的時候無法還原解決辦法就是在MASTER數據庫中添加一個存儲過程

  create proc killspid (@dbname varchar())

  as begin declare @sql nvarchar(

  declare @spid int set @sql=declare getspid cursor for select spid from sysprocesses where dbid=db_id(+@dbname+ exec (@sql)

  open getspid fetch next from getspid into @spid while @@fetch_status<> begin exec(kill +@spid)

  fetch next from getspid into @spid end close getspid deallocate getspid end GO

  在還原之前先執行這個存儲過程需要傳遞dbname就是你的數據庫的名字下邊是類的原代碼(nfig裡的數據庫連接字符串是constr)

  using System

  using SystemConfiguration

  using SystemDataSqlClient

  using SystemData

  namespace webbase_class

  {

  /// <summary>

  /// DbOper類主要應用SQLDMO實現對Microsoft SQL Server數據庫的備份和恢復

  /// </summary>

  public class DbOper

  {

  private string server

  private string uid

  private string pwd

  private string database

  private string conn

  /// <summary>

  /// DbOper類的構造函數

  /// </summary>

  public DbOper()

  {

  conn=SystemConfigurationConfigurationSettingsAppSettings[constr]ToString()

  server=cut(connserver=

  uid=cut(connuid=

  pwd=cut(connpwd=

  database=cut(conndatabase=

  }

  public string cut(string strstring bgstring ed)

  {

  string sub

  sub=strSubstring(strIndexOf(bg)+bgLength)

  sub=subSubstring(subIndexOf())

  return sub

  }

  /// <summary>

  /// 數據庫備份

  /// </summary>

  public bool DbBackup(string url)

  {

  SQLDMOBackup oBackup = new SQLDMOBackupClass()

  SQLDMOSQLServer oSQLServer = new SQLDMOSQLServerClass()

  try

  {

  oSQLServerLoginSecure = false

  oSQLServerConnect(serveruid pwd)

  oBackupAction = SQLDMOSQLDMO_BACKUP_TYPESQLDMOBackup_Database

  oBackupDatabase = database

  oBackupFiles = url//d\Northwindbak

  oBackupBackupSetName = database

  oBackupBackupSetDescription = 數據庫備份

  oBackupInitialize = true

  oBackupSQLBackup(oSQLServer)

  return true

  }

  catch

  {

  return false

  throw

  }

  finally

  {

  oSQLServerDisConnect()

  }

  }

  /// <summary>

  /// 數據庫恢復

  /// </summary>

  public string DbRestore(string url)

  {

  if(exepro()!=true)//執行存儲過程

  {

  return 操作失敗

  }

  else

  {

  SQLDMORestore oRestore = new SQLDMORestoreClass()

  SQLDMOSQLServer oSQLServer = new SQLDMOSQLServerClass()

  try

  {

  oSQLServerLoginSecure = false

  oSQLServerConnect(server uid pwd)

  oRestoreAction = SQLDMOSQLDMO_RESTORE_TYPESQLDMORestore_Database

  oRestoreDatabase = database

  oRestoreFiles = url//@d\Northwindbak

  oRestoreFileNumber =

  oRestoreReplaceDatabase = true

  oRestoreSQLRestore(oSQLServer)

  return ok

  }

  catch(Exception e)

  {

  return 恢復數據庫失敗

  throw

  }

  finally

  {

  oSQLServerDisConnect()

  }

  }

  } /FooterTemplate> </aspTemplateColumn> </Columns> </aspdatagrid>> </aspTemplateColumn> </Columns> </aspdatagrid>

  private bool exepro()

  {

  SqlConnection conn = new SqlConnection(server=+server+uid=+uid+pwd=+pwd+database=master

  SqlCommand cmd = new SqlCommand(killspidconn

  cmdCommandType = CommandTypeStoredProcedure

  cmdParametersAdd(@dbnameport

  try

  {

  connOpen()

  cmdExecuteNonQuery()

  return true

  }

  catch(Exception ex)

  {

  return false

  }

  finally

  {

  connClose()

  }

  }

  }

  }


From:http://tw.wingwit.com/Article/program/net/201311/11994.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.