。。
问题是假如我备份了一次,表里有3个数据起名为bak此时不进行恢复,我在向表里添3个数据进行备份起名也为bak对第一次进行覆盖,现在表里应有6个数据了,但我这时恢复完后,表里应有6个数据对巴,可是恢复的只是第一次时的3个,要是备份第二次时起名不是bak,不对第一次进行覆盖就没有上述现象!请看我代码有那些不足吗?请高手修改,谢了!!!!
。。
备份的代码:
Public Sub bakeupdatabase()
Dim cnn As New ADODB.Connection
Dim s_path As String
Dim s_dataexport As String
Me.MousePointer = 11
s_dataexport = "backup database JSDJ to disk='" + CDG1.FileName + "'"
cnn.Open "driver={sql server};server=" & JSDJ & ";database=JSDJ;Persist Security Info=False;User ID=sa;"
cnn.BeginTrans
cnn.Execute s_dataexport
If Err.Number = 0 Then
cnn.CommitTrans
MsgBox "数据备份成功!", vbInformation, "提示"
MsgBox "数据备份的路径是:" & CDG1.FileName, vbOKOnly, "提示"
Unload Me
Else
cnn.RollbackTrans
MsgBox "数据备份失败!请检查数据库是否打开!", vbCritical, "提示"
End If
cnn.Close
Set cnn = Nothing
Me.MousePointer = 1
End Sub
。。
恢复的代码:
If Text1.Text = "" Then
MsgBox "请选择要恢复的文件!", vbInformation, "提示"
Text1.SetFocus
Exit Sub
Else
ret = MsgBox("数据恢复操作会覆盖以前的所有数据并且覆盖后无法恢复,您确定要恢复吗?", vbQuestion + vbOKCancel, "提示")
If ret = vbOK Then
Dim cnn As New ADODB.Connection
Dim sn As New ADODB.Recordset
Dim s_restore As String
' Set sn = Nothing
Me.MousePointer = 11
cnn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;server=" & JSDJ & ";Initial Catalog=master;data source=127.0.0.1;User ID=sa "
sn.Open "select spid from sysprocesses where dbid=db_id('JSDJ')", cnn
Do While Not sn.EOF
cnn.Execute "kill " & sn("spid")
sn.MoveNext
Loop
sn.Close
s_restore = "restore database JSDJ from disk='" + Trim(Text1.Text) + "' with replace "
cnn.Execute s_restore
's_restore = "restore database JSDJ1 from disk='" + Trim(Text1.Text) + "'"
' cnn.Execute s_restore
cnn.BeginTrans
If Err.Number = 0 Then
cnn.CommitTrans
MsgBox "数据恢复成功!", vbInformation, "提示"
Else
cnn.RollbackTrans
MsgBox "数据恢复失败!", vbInformation, "提示"
End If
cnn.Close
Set cnn = Nothing
Me.MousePointer = 1
Else
Exit Sub
End If
End If
问题是假如我备份了一次,表里有3个数据起名为bak此时不进行恢复,我在向表里添3个数据进行备份起名也为bak对第一次进行覆盖,现在表里应有6个数据了,但我这时恢复完后,表里应有6个数据对巴,可是恢复的只是第一次时的3个,要是备份第二次时起名不是bak,不对第一次进行覆盖就没有上述现象!请看我代码有那些不足吗?请高手修改,谢了!!!!
。。
备份的代码:
Public Sub bakeupdatabase()
Dim cnn As New ADODB.Connection
Dim s_path As String
Dim s_dataexport As String
Me.MousePointer = 11
s_dataexport = "backup database JSDJ to disk='" + CDG1.FileName + "'"
cnn.Open "driver={sql server};server=" & JSDJ & ";database=JSDJ;Persist Security Info=False;User ID=sa;"
cnn.BeginTrans
cnn.Execute s_dataexport
If Err.Number = 0 Then
cnn.CommitTrans
MsgBox "数据备份成功!", vbInformation, "提示"
MsgBox "数据备份的路径是:" & CDG1.FileName, vbOKOnly, "提示"
Unload Me
Else
cnn.RollbackTrans
MsgBox "数据备份失败!请检查数据库是否打开!", vbCritical, "提示"
End If
cnn.Close
Set cnn = Nothing
Me.MousePointer = 1
End Sub
。。
恢复的代码:
If Text1.Text = "" Then
MsgBox "请选择要恢复的文件!", vbInformation, "提示"
Text1.SetFocus
Exit Sub
Else
ret = MsgBox("数据恢复操作会覆盖以前的所有数据并且覆盖后无法恢复,您确定要恢复吗?", vbQuestion + vbOKCancel, "提示")
If ret = vbOK Then
Dim cnn As New ADODB.Connection
Dim sn As New ADODB.Recordset
Dim s_restore As String
' Set sn = Nothing
Me.MousePointer = 11
cnn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;server=" & JSDJ & ";Initial Catalog=master;data source=127.0.0.1;User ID=sa "
sn.Open "select spid from sysprocesses where dbid=db_id('JSDJ')", cnn
Do While Not sn.EOF
cnn.Execute "kill " & sn("spid")
sn.MoveNext
Loop
sn.Close
s_restore = "restore database JSDJ from disk='" + Trim(Text1.Text) + "' with replace "
cnn.Execute s_restore
's_restore = "restore database JSDJ1 from disk='" + Trim(Text1.Text) + "'"
' cnn.Execute s_restore
cnn.BeginTrans
If Err.Number = 0 Then
cnn.CommitTrans
MsgBox "数据恢复成功!", vbInformation, "提示"
Else
cnn.RollbackTrans
MsgBox "数据恢复失败!", vbInformation, "提示"
End If
cnn.Close
Set cnn = Nothing
Me.MousePointer = 1
Else
Exit Sub
End If
End If
cnn.Execute s_restore
's_restore = "restore database JSDJ1 from disk='" + Trim(Text1.Text) + "'"
' cnn.Execute s_restore
cnn.BeginTrans
......
事务开始语句(cnn.BeginTrans)的位置都搞错了。仔细检查先。