转载
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 = "[test_DATA]" + "," + "[" + Trim(txtSQLPath.Text) + "\" + Trim(TxtDB.Text) + "_data.mdf]"
str = str + ",[test_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 Sub
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 = "[test_DATA]" + "," + "[" + Trim(txtSQLPath.Text) + "\" + Trim(TxtDB.Text) + "_data.mdf]"
str = str + ",[test_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 Sub
你要使用master数据库才行
cn.execut "use master"
数据备份的时候可以不用master
但是数据恢复的时候必须使用master数据库