我编写了一个数据库备份程序程序如下:
Dim StrCnn As New Connection '定义连接
Dim itmX As ListItem '定义一个ListItem对象
Dim key, list, sql As String '定义字符串变量Private Sub Form_Load()
Option1.Value = True '设置Option1的值为真
Dir1_Change
End Sub
Private Sub Dir1_Change()
File1.Path = Dir1.Path
'添加数据备份卡到列表中
ListView1.ListItems.Clear
If File1.ListCount <> 0 Then
a = 0 '给变量a设置初值
Do While File1.ListIndex < File1.ListCount - 1
File1.ListIndex = a '赋值给变量a
key = File1.FileName
Set itmX = ListView1.ListItems.Add(, , key, 1)
a = a + 1
Loop
End If
File1_Click
End Sub
Private Sub Drive1_Change()
Dir1.Path = Drive1.Drive
End Sub
Private Sub File1_Click()
Label1.Caption = Dir1.Path & "\" & File1.FileName '获得路径
End Sub
Private Sub ListView1_Click()
Label1.Caption = Dir1.Path & "\" & ListView1.SelectedItem '获得路径
End Sub
Private Sub CmdBackup_Click()
Dim S
Dim m As String
If Option1.Value = True Then
'备份数据库
m = Format(Time, "hh-nn-ss")
S = Dir1.Path & "\" & Date & "-" & m & ".BAK"
If Label1.Caption = S Then
MsgBox "数据备份卡已存在!"
Exit Sub
Else
StrCnn.Open "Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data Source=Backup"
'数据备份语句
sql = "backup DATABASE bookmanage TO disk='" & S & "'"
StrCnn.Execute (sql) '执行SQL语句
StrCnn.Close
MsgBox "数据库备份成功!"
key = Date & "-" & m & ".BAK"
Set itmX = ListView1.ListItems.Add(, , key, 1)
ListView1.Refresh
File1.Refresh
End If
End If
If Option2.Value = True Then
'恢复指定路径下的数据库
If File1.ListCount <> 0 Then
If Label1.Caption <> "" Then
StrCnn.Open "Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data Source=Backup"
'数据恢复语句
sql = "RESTORE DATABASE bookmanage from disk='" & Label1.Caption & "'"
On Error Resume Next
StrCnn.Execute (sql) '执行SQL语句
StrCnn.Close
MsgBox "数据库恢复成功!"
Else
MsgBox "请选择要恢复的数据备份卡!"
End If
Else
MsgBox "请选择要恢复的数据备份卡!"
End If
End If
End Sub
Private Sub CmdEnd_Click()
End
End Sub可是进行恢复的时候我发现不对啊?
不是BACKUP可以备份整个数据库吗?但是我恢复怎么什么都不能恢复出来啊?比如我先进行了备份然后在数据库中添加了一些数据。用以前的备份恢复但是还是有添加的那些数据啊??
请问高手是怎么回事啊??
Dim StrCnn As New Connection '定义连接
Dim itmX As ListItem '定义一个ListItem对象
Dim key, list, sql As String '定义字符串变量Private Sub Form_Load()
Option1.Value = True '设置Option1的值为真
Dir1_Change
End Sub
Private Sub Dir1_Change()
File1.Path = Dir1.Path
'添加数据备份卡到列表中
ListView1.ListItems.Clear
If File1.ListCount <> 0 Then
a = 0 '给变量a设置初值
Do While File1.ListIndex < File1.ListCount - 1
File1.ListIndex = a '赋值给变量a
key = File1.FileName
Set itmX = ListView1.ListItems.Add(, , key, 1)
a = a + 1
Loop
End If
File1_Click
End Sub
Private Sub Drive1_Change()
Dir1.Path = Drive1.Drive
End Sub
Private Sub File1_Click()
Label1.Caption = Dir1.Path & "\" & File1.FileName '获得路径
End Sub
Private Sub ListView1_Click()
Label1.Caption = Dir1.Path & "\" & ListView1.SelectedItem '获得路径
End Sub
Private Sub CmdBackup_Click()
Dim S
Dim m As String
If Option1.Value = True Then
'备份数据库
m = Format(Time, "hh-nn-ss")
S = Dir1.Path & "\" & Date & "-" & m & ".BAK"
If Label1.Caption = S Then
MsgBox "数据备份卡已存在!"
Exit Sub
Else
StrCnn.Open "Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data Source=Backup"
'数据备份语句
sql = "backup DATABASE bookmanage TO disk='" & S & "'"
StrCnn.Execute (sql) '执行SQL语句
StrCnn.Close
MsgBox "数据库备份成功!"
key = Date & "-" & m & ".BAK"
Set itmX = ListView1.ListItems.Add(, , key, 1)
ListView1.Refresh
File1.Refresh
End If
End If
If Option2.Value = True Then
'恢复指定路径下的数据库
If File1.ListCount <> 0 Then
If Label1.Caption <> "" Then
StrCnn.Open "Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data Source=Backup"
'数据恢复语句
sql = "RESTORE DATABASE bookmanage from disk='" & Label1.Caption & "'"
On Error Resume Next
StrCnn.Execute (sql) '执行SQL语句
StrCnn.Close
MsgBox "数据库恢复成功!"
Else
MsgBox "请选择要恢复的数据备份卡!"
End If
Else
MsgBox "请选择要恢复的数据备份卡!"
End If
End If
End Sub
Private Sub CmdEnd_Click()
End
End Sub可是进行恢复的时候我发现不对啊?
不是BACKUP可以备份整个数据库吗?但是我恢复怎么什么都不能恢复出来啊?比如我先进行了备份然后在数据库中添加了一些数据。用以前的备份恢复但是还是有添加的那些数据啊??
请问高手是怎么回事啊??
代码如下:
Command1:备份
Command2:恢复备份
Combo1:里面是数据库名称,用来选择Dim oSQLServer As SQLDMO.SQLServer '服务器
Dim WithEvents oBackupEvent As SQLDMO.Backup '备份事件
Dim WithEvents oRestoreEvent As SQLDMO.Restore '还原事件
Dim bakfilename As String
Dim flag As Boolean//备份数据库
Private Sub Command1_Click()
On Error GoTo err
err.Clear
Dim oBackup As SQLDMO.Backup
Set oBackup = New SQLDMO.Backup '备份对象实例化
Set oBackupEvent = oBackup '备份事件实例化'设置两个重要属性:Database以及Files
oBackup.Database = Combo1.Text
oBackup.Files = bakfilenameIf Len(Dir(bakfilename)) > 0 Then
Kill bakfilename
End IfoBackup.SQLBackup oSQLServer
Set oBackupEvent = Nothing
Set oBackup = NothingMsgBox "Backup DB successfully!"
Exit Sub
err:
MsgBox err.Description
Resume Next
End Sub
//恢复备份
Private Sub Command2_Click()
On Error GoTo err
err.Clear
Dim oRestore As SQLDMO.Restore
Set oRestore = New SQLDMO.Restore
Set oRestoreEvent = oRestore
'同样设置Database,Files两个属性来还原
oRestore.Database = Combo1.Text
oRestore.Files = bakfilenameIf Len(Dir(bakfilename)) = 0 Then
MsgBox "Can't find the bak file!", vbCritical
Exit Sub
End IfoRestore.SQLRestore oSQLServer
Set oRestoreEvent = Nothing
Set oRestore = NothingMsgBox "Restore DB successfully!"
Exit Suberr:
MsgBox err.Description
Resume Next
End Sub//退出
Private Sub Command3_Click()If flag = True Then oSQLServer.DisConnect '如果标志为true则先断开连接
If Not oSQLServer Is Nothing Then
Set oSQLServer = Nothing
End IfUnload Me
End Sub//注意,在LOAD窗体的时候,自动提取当前服务器中非系统数据库(如Master等是系统数据库)自动填充到Combo1
Private Sub Form_Load()
bakfilename = "d:\DB.bak"
flag = False
On Error GoTo err
err.Clear
Combo1.Clear
If oSQLServer Is Nothing Then
Set oSQLServer = New SQLDMO.SQLServer
End If
oSQLServer.Connect "FHH", "sa", ""
'这个第一个参数是服务器名称,第二和第三是用户名和密码
flag = True
Dim oDB As SQLDMO.Database
For Each oDB In oSQLServer.Databases
If oDB.SystemObject = False Then
Combo1.AddItem oDB.Name
End If
Next
Combo1.ListIndex = 0 '默认选择为第一项
Exit Sub
err:
MsgBox err.Description
Resume Next
End SubPrivate Sub oBackupEvent_Complete(ByVal Message As String)
'
End SubPrivate Sub oBackupEvent_NextMedia(ByVal Message As String)
'
End SubPrivate Sub oBackupEvent_PercentComplete(ByVal Message As String, ByVal Percent As Long)
'
End SubPrivate Sub oRestoreEvent_Complete(ByVal Message As String)
'
End SubPrivate Sub oRestoreEvent_NextMedia(ByVal Message As String)
'
End SubPrivate Sub oRestoreEvent_PercentComplete(ByVal Message As String, ByVal Percent As Long)
'
End Sub
请注意:对于oRestoreEvent和oBackupEvent两个对象的Complete ,PercentComplete ,NextMedia这六个事件中一定要有一个注释符号,不然会出错。具体原因我也不清楚,楼主自己可以去研究一下。好了,就这样,赶快结帖哦!!哈哈~~~先谢谢先!
才可以使用SQLDBO哦!