下面是还原数据库代码,只要修改Server名称与数据库名称即可执行Dim Cn As ADODb.Connection Dim Rs As ADODb.Recordset Dim response, msg, style, title As String style = "是否恢复此数据库?恢复后现在所有数据将丢失!" msg = vbYesNo + vbQuestion + vbDefaultButton1 title = "警告信息!" response = MsgBox(style, msg, title) Dialog1.FileName = "" If response = vbYes Then Dim Path As String Dialog1.Action = 1 Path = Dialog1.FileName If Path <> "" Then Set Cn = New ADODb.Connection Cn.Open "Provider=SQLOLEDB.1;Password=;Persist Security Info=False;User ID=sa;Initial Catalog=master;Data Source=机器名" Cn.CursorLocation = adUseClient s = "use [master]" Set Rs = New ADODb.Recordset With Rs .ActiveConnection = Cn .CursorType = adOpenDynamic .LockType = adLockOptimistic .Source = s .Open End With s = "select DISTINCT physical_name,logical_name, file_type from msdb..backupfile where backup_set_id in (6)" Set Rs = New ADODb.Recordset With Rs .ActiveConnection = Cn .CursorType = adOpenDynamic .LockType = adLockOptimistic .Source = s .Open End With s = "set noexec off set parseonly off" Set Rs = New ADODb.Recordset With Rs .ActiveConnection = Cn .CursorType = adOpenDynamic .LockType = adLockOptimistic .Source = s .Open End With s = "RESTORE DATABASE [要还原的数据库名] FROM DISK = N'" & Path & "' WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY" Set Rs = New ADODb.Recordset With Rs .ActiveConnection = Cn .CursorType = adOpenDynamic .LockType = adLockOptimistic .Source = s .Open End With s = "select name, DATABASEPROPERTY(name, N'IsDetached'), DATABASEPROPERTY(name, N'IsShutdown'), DATABASEPROPERTY(name, N'IsSuspect'), DATABASEPROPERTY(name, N'IsOffline'), DATABASEPROPERTY(name, N'IsInLoad'), DATABASEPROPERTY(name, N'IsInRecovery'), DATABASEPROPERTY(name, N'IsNotRecovered'), DATABASEPROPERTY(name, N'IsEmergencyMode'), DATABASEPROPERTY(name, N'IsInStandBy'), status, category, status2 from master..sysdatabases" Set Rs = New ADODb.Recordset With Rs .ActiveConnection = Cn .CursorType = adOpenDynamic .LockType = adLockOptimistic .Source = s .Open End With Set Rs = Nothing MsgBox "数据库还原成功", 0 + 64 End If End If
Option ExplicitDim dmoServer As SQLDMO.SQLServer Dim dmoRestore As SQLDMO.Restore Dim WithEvents dmoRestoreEvent As SQLDMO.Restore
Private Sub RestoreDBFromFile(ServerName As String, DBName As String, BackupToRestore As String) Dim str As String Dim ifExistDB As Boolean On Error GoTo Handler
'判断参数 If ServerName = "" Or DBName = "" Or BackupToRestore = "" Then MsgBox "服务器名称,数据库名称,数据库文件不能为空!", vbInformation + vbOKOnly, "错误提示!" Exit Sub End If
'打开SQL-Server的连接 Set dmoServer = New SQLDMO.SQLServer With dmoServer .LoginSecure = True .Connect ServerName End With
'新的SQL-DMO 的恢复对象 Set dmoRestore = New SQLDMO.Restore '启动事件 Set dmoRestoreEvent = dmoRestore
If Err.Number = -2147221499 Then Resume Next Else MsgBox Err.Number & vbCrLf & Err.Description Screen.MousePointer = vbDefault End If End SubPrivate Sub cmdClose_Click() Unload Me End SubPrivate Sub cmdFile_Click() dlgFile.Filter = "全部文件(*.*)|*.*" dlgFile.CancelError = False dlgFile.ShowOpen If dlgFile.FileName = "" Then Exit Sub txtPath.Text = dlgFile.FileName End SubPrivate Sub cmdRestore_Click() Call RestoreDBFromFile(txtServer.Text, TxtDB.Text, txtPath.Text) End Sub Private Sub cmdSQL_Click() txtSQLPath.Text = OpenDirectoryTV(frmRestore, "SQL-Server的安装路径!") End SubPrivate Sub dmoRestoreEvent_PercentComplete(ByVal Message As String, ByVal Percent As Long) PGSQL.Value = Percent PGSQL.Refresh
DoEvents End Sub Public Function OpenDirectoryTV(odtvOwner As Form, Optional odtvTitle As String) As String Dim lpIDList As Long Dim sBuffer As String Dim szTitle As String Dim tBrowseInfo As BrowseInfo szTitle = odtvTitle With tBrowseInfo .hwndOwner = odtvOwner.hWnd .lpszTitle = lstrcat(szTitle, "") .ulFlags = BIF_RETURNONLYFSDIRS + BIF_DONTGOBELOWDOMAIN End With lpIDList = SHBrowseForFolder(tBrowseInfo) If (lpIDList) Then sBuffer = Space(MAX_PATH) SHGetPathFromIDList lpIDList, sBuffer sBuffer = Left(sBuffer, InStr(sBuffer, vbNullChar) - 1) OpenDirectoryTV = sBuffer End If End Function Private Sub Form_Load() '========================================= ' 读取服务器名 Dim sqlApp As New SQLDMO.Application Dim NL As SQLDMO.NameList Dim Index As Integer
Set NL = sqlApp.ListAvailableSQLServers For Index = 1 To NL.Count txtServer.AddItem NL.Item(Index) Next Set sqlApp = Nothing '========================================= txtPath.Text = App.Path
End SubPrivate Sub txtServer_Change() On Error Resume Next
txtSQLPath.Text = "" Set dmoServer = New SQLDMO.SQLServer dmoServer.LoginSecure = True dmoServer.Connect Trim(txtServer.Text) txtSQLPath.Text = dmoServer.Registry.SQLDataRoot + "\Data" Set dmoServer = Nothing End SubPrivate Sub txtServer_Click() txtServer_Change End Sub
sql="RESTORE DATABASE DatabaseName FROM DISK = '路径'" 建立连接cn cn.execute sql 注意恢复你的数据库的时候最好连接到master数据库
Dim Rs As ADODb.Recordset
Dim response, msg, style, title As String
style = "是否恢复此数据库?恢复后现在所有数据将丢失!"
msg = vbYesNo + vbQuestion + vbDefaultButton1
title = "警告信息!"
response = MsgBox(style, msg, title)
Dialog1.FileName = ""
If response = vbYes Then
Dim Path As String
Dialog1.Action = 1
Path = Dialog1.FileName
If Path <> "" Then
Set Cn = New ADODb.Connection
Cn.Open "Provider=SQLOLEDB.1;Password=;Persist Security Info=False;User ID=sa;Initial Catalog=master;Data Source=机器名"
Cn.CursorLocation = adUseClient
s = "use [master]"
Set Rs = New ADODb.Recordset
With Rs
.ActiveConnection = Cn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "select DISTINCT physical_name,logical_name, file_type from msdb..backupfile where backup_set_id in (6)"
Set Rs = New ADODb.Recordset
With Rs
.ActiveConnection = Cn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "set noexec off set parseonly off"
Set Rs = New ADODb.Recordset
With Rs
.ActiveConnection = Cn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "RESTORE DATABASE [要还原的数据库名] FROM DISK = N'" & Path & "' WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY"
Set Rs = New ADODb.Recordset
With Rs
.ActiveConnection = Cn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
s = "select name, DATABASEPROPERTY(name, N'IsDetached'), DATABASEPROPERTY(name, N'IsShutdown'), DATABASEPROPERTY(name, N'IsSuspect'), DATABASEPROPERTY(name, N'IsOffline'), DATABASEPROPERTY(name, N'IsInLoad'), DATABASEPROPERTY(name, N'IsInRecovery'), DATABASEPROPERTY(name, N'IsNotRecovered'), DATABASEPROPERTY(name, N'IsEmergencyMode'), DATABASEPROPERTY(name, N'IsInStandBy'), status, category, status2 from master..sysdatabases"
Set Rs = New ADODb.Recordset
With Rs
.ActiveConnection = Cn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With
Set Rs = Nothing
MsgBox "数据库还原成功", 0 + 64
End If
End If
Dim dmoRestore As SQLDMO.Restore
Dim WithEvents dmoRestoreEvent As SQLDMO.Restore
Private Sub RestoreDBFromFile(ServerName As String, DBName As String, BackupToRestore As String)
Dim str As String
Dim ifExistDB As Boolean
On Error GoTo Handler
'判断参数
If ServerName = "" Or DBName = "" Or BackupToRestore = "" Then
MsgBox "服务器名称,数据库名称,数据库文件不能为空!", vbInformation + vbOKOnly, "错误提示!"
Exit Sub
End If
'打开SQL-Server的连接
Set dmoServer = New SQLDMO.SQLServer
With dmoServer
.LoginSecure = True
.Connect ServerName
End With
'新的SQL-DMO 的恢复对象
Set dmoRestore = New SQLDMO.Restore
'启动事件
Set dmoRestoreEvent = dmoRestore
With dmoRestore
'恢复对象的数据库名称
.Database = DBName
'指定操作
.Action = SQLDMORestore_Database
'强制覆盖现有数据库
.ReplaceDatabase = True
'从文件中恢复
.Files = BackupToRestore
'匹配逻辑名称 和 物理名称(成对出现)
str = "[XDSMS40_DATA]" + "," + "[" + Trim(txtSQLPath.Text) + "\" + Trim(TxtDB.Text) + "_data.mdf]"
str = str + ",[XDSMS40_Log]" + "," + "[" + Trim(txtSQLPath.Text) + "\" + Trim(TxtDB.Text) + "_log.LDF]"
.RelocateFiles = str
'开始恢复
Screen.MousePointer = vbHourglass
.PercentCompleteNotification = 1
Frame1.Enabled = False
cmdRestore.Enabled = False
cmdClose.Enabled = False
.SQLRestore dmoServer
End With
'下面Verify被恢复的数据库
dmoRestore.SQLVerify dmoServer
If PGSQL.Value = 100 Then
PGSQL.Visible = False
lblPercent.Caption = "数据库" & TxtDB.Text & "已经成功被恢复!"
lblPercent.Visible = True
End If
'对象关闭
Set dmoRestore = Nothing
dmoServer.DisConnect
Set dmoServer = Nothing
Screen.MousePointer = vbDefault
Frame1.Enabled = True
cmdRestore.Enabled = True
cmdClose.Enabled = True Exit Sub
Handler:
Frame1.Enabled = True
cmdRestore.Enabled = True
cmdClose.Enabled = True
If Err.Number = -2147221499 Then
Resume Next
Else
MsgBox Err.Number & vbCrLf & Err.Description
Screen.MousePointer = vbDefault
End If
End SubPrivate Sub cmdClose_Click()
Unload Me
End SubPrivate Sub cmdFile_Click()
dlgFile.Filter = "全部文件(*.*)|*.*"
dlgFile.CancelError = False
dlgFile.ShowOpen
If dlgFile.FileName = "" Then Exit Sub
txtPath.Text = dlgFile.FileName
End SubPrivate Sub cmdRestore_Click()
Call RestoreDBFromFile(txtServer.Text, TxtDB.Text, txtPath.Text)
End Sub
Private Sub cmdSQL_Click()
txtSQLPath.Text = OpenDirectoryTV(frmRestore, "SQL-Server的安装路径!")
End SubPrivate Sub dmoRestoreEvent_PercentComplete(ByVal Message As String, ByVal Percent As Long)
PGSQL.Value = Percent
PGSQL.Refresh
DoEvents
End Sub
Public Function OpenDirectoryTV(odtvOwner As Form, Optional odtvTitle As String) As String
Dim lpIDList As Long
Dim sBuffer As String
Dim szTitle As String
Dim tBrowseInfo As BrowseInfo
szTitle = odtvTitle
With tBrowseInfo
.hwndOwner = odtvOwner.hWnd
.lpszTitle = lstrcat(szTitle, "")
.ulFlags = BIF_RETURNONLYFSDIRS + BIF_DONTGOBELOWDOMAIN
End With
lpIDList = SHBrowseForFolder(tBrowseInfo)
If (lpIDList) Then
sBuffer = Space(MAX_PATH)
SHGetPathFromIDList lpIDList, sBuffer
sBuffer = Left(sBuffer, InStr(sBuffer, vbNullChar) - 1)
OpenDirectoryTV = sBuffer
End If
End Function
Private Sub Form_Load()
'=========================================
' 读取服务器名
Dim sqlApp As New SQLDMO.Application
Dim NL As SQLDMO.NameList
Dim Index As Integer
Set NL = sqlApp.ListAvailableSQLServers
For Index = 1 To NL.Count
txtServer.AddItem NL.Item(Index)
Next
Set sqlApp = Nothing
'========================================= txtPath.Text = App.Path
End SubPrivate Sub txtServer_Change()
On Error Resume Next
txtSQLPath.Text = "" Set dmoServer = New SQLDMO.SQLServer
dmoServer.LoginSecure = True
dmoServer.Connect Trim(txtServer.Text)
txtSQLPath.Text = dmoServer.Registry.SQLDataRoot + "\Data"
Set dmoServer = Nothing
End SubPrivate Sub txtServer_Click()
txtServer_Change
End Sub
建立连接cn
cn.execute sql
注意恢复你的数据库的时候最好连接到master数据库