有关SQL Server数据库的备份与恢复 ********************************************************* '* 名称:BackupDatabase '* 功能:备份数据库 '* 控件:一个文本框和两个按钮(备份到和确定) '********************************************************* Public Sub BackupDatabase() Dim cn As New ADODB.Connection Dim s_path, s_dataexport As String s_path = App.Path Me.MousePointer = 11 '设置鼠标指针形状 'student1是需要备份的数据库名称 s_dataexport = "backup database student1 to disk='" + CommonDialog1.FileName + "'" cn.Open "driver={sql server};server=" & d1 & ";database=student1;persist security info=false; userid=sa" '数据库连接字符串 '这里不需要连接master数据库,即可完成备份 cn.BeginTrans cn.Execute s_dataexport Err.Number = 0 If Err.Number = 0 Then cn.CommitTrans MsgBox "数据备份成功!", vbInformation, "提示" MsgBox "数据备份文件存放路径:" & CommonDialog1.FileName, vbOKOnly, "提示" Unload Me Else cn.RollbackTrans MsgBox "数据备份失败!请检查数据库是否正在打开!", vbCritical, "提示" End If cn.Close Set cn = Nothing Me.MousePointer = 1 End Sub'********************************************************* '* 名称:RestoreDataBase '* 功能:还原数据库 '* 控件:一个文本框和两个按钮( 打开和确定) '********************************************************* Public Sub RestoreDataBase() If Text1.Text = "" Then MsgBox "请选择要恢复的数据文件!", vbInformation, "提示" Exit Sub Else ret = MsgBox("数据恢复操作将会覆盖以前的所有数据并且覆盖后无法恢复,您确定要进行恢复操作吗?", vbQuestion + vbOKCancel, "提示") If ret = vbOK Then Dim cn As New ADODB.Connection Dim sn As New ADODB.Recordset Dim s_restore As String Me.MousePointer = 11 cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;server=" & d1 & ";Initial Catalog=master;Data Source=127.0.0.1;user id=sa;password=" & d3 & "" sn.Open "select spid from sysprocesses where dbid=db_id('student1')", cn Do While Not sn.EOF cn.Execute "kill " & sn("spid") sn.MoveNext Loop sn.Close s_restore = "restore database student1 from disk='" + Trim(Text1.Text) + "' with REPLACE" cn.Execute s_restore 'Debug.Print gs_conn_string '此时需要连接master数据库才能完成数据恢复操作 '同上student1为需要恢复的数据库 s_restore = "restore database student1 from disk='" + Trim(Text1.Text) + "'" 'text1一个用于记录需要恢复文件的地址的textbox cn.Execute s_restore cn.BeginTrans If Err.Number = 0 Then cn.CommitTrans MsgBox "数据恢复成功!", vbInformation, "提示" Command1.Enabled = True Label1.Visible = False Else cn.RollbackTrans MsgBox "数据恢复失败!", vbCritical, "提示" Command1.Enabled = True End If cn.Close Set cn = Nothing Me.MousePointer = 1 Else Exit Sub End If ''''''''''''''''''''''''''''''''''''''''' On Error Resume Next Dim DBC As New DataBaseConnection If db.State = 1 Then db.Close End If db.ConnectionString = DBC.SqlConnectString(d1, d2, d3) rs.CursorType = adOpenDynamic rs.CursorLocation = adUseClient rs.LockType = adLockOptimistic db.CursorLocation = adUseClient db.Open Set cmd.ActiveConnection = db If Err.Number Then MsgBox Err.Description, 16 + vbOKOnly, Err.Number Exit Sub End If db.DefaultDatabase = "student1" If Err.Number Then MsgBox Err.Description, 16 + vbOKOnly, Err.Number Exit Sub End If End If End Sub '''''''''''''''''''''''''''''''''''''''''''''如果当前没有与要恢复的数据库立连接,则不需要加单引号中的内容。如果希望恢复数据库之后继续建立连接,则需要写这部分代码。我要恢复数据库名称为student1,备份数据库的时候是在连接状态下进行的,但是恢复数据库不可以在数据库存在连接的状态下进行操作!代码的解决方法是:先连接SQL Server中主库master 库,在该库中的sysprocesses表中存放着所有连接到此数据库的连接信息,将这些连接信息用Kill语句删除。然后再恢复数据库student1,由于用Kill语句后,数据库已经被断开,所以在恢复完成后,再用系统最初的连接数据库代码连接上数据库student1。
*********************************************************
'* 名称:BackupDatabase
'* 功能:备份数据库
'* 控件:一个文本框和两个按钮(备份到和确定)
'*********************************************************
Public Sub BackupDatabase()
Dim cn As New ADODB.Connection
Dim s_path, s_dataexport As String
s_path = App.Path
Me.MousePointer = 11 '设置鼠标指针形状
'student1是需要备份的数据库名称
s_dataexport = "backup database student1 to disk='" + CommonDialog1.FileName + "'"
cn.Open "driver={sql server};server=" & d1 & ";database=student1;persist security info=false; userid=sa" '数据库连接字符串
'这里不需要连接master数据库,即可完成备份
cn.BeginTrans
cn.Execute s_dataexport
Err.Number = 0
If Err.Number = 0 Then
cn.CommitTrans
MsgBox "数据备份成功!", vbInformation, "提示"
MsgBox "数据备份文件存放路径:" & CommonDialog1.FileName, vbOKOnly, "提示"
Unload Me
Else
cn.RollbackTrans
MsgBox "数据备份失败!请检查数据库是否正在打开!", vbCritical, "提示"
End If
cn.Close
Set cn = Nothing
Me.MousePointer = 1
End Sub'*********************************************************
'* 名称:RestoreDataBase
'* 功能:还原数据库
'* 控件:一个文本框和两个按钮( 打开和确定)
'*********************************************************
Public Sub RestoreDataBase()
If Text1.Text = "" Then
MsgBox "请选择要恢复的数据文件!", vbInformation, "提示"
Exit Sub
Else
ret = MsgBox("数据恢复操作将会覆盖以前的所有数据并且覆盖后无法恢复,您确定要进行恢复操作吗?", vbQuestion + vbOKCancel, "提示")
If ret = vbOK Then
Dim cn As New ADODB.Connection
Dim sn As New ADODB.Recordset
Dim s_restore As String
Me.MousePointer = 11
cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;server=" & d1 & ";Initial Catalog=master;Data Source=127.0.0.1;user id=sa;password=" & d3 & ""
sn.Open "select spid from sysprocesses where dbid=db_id('student1')", cn
Do While Not sn.EOF
cn.Execute "kill " & sn("spid")
sn.MoveNext
Loop
sn.Close
s_restore = "restore database student1 from disk='" + Trim(Text1.Text) + "' with REPLACE"
cn.Execute s_restore
'Debug.Print gs_conn_string
'此时需要连接master数据库才能完成数据恢复操作
'同上student1为需要恢复的数据库
s_restore = "restore database student1 from disk='" + Trim(Text1.Text) + "'"
'text1一个用于记录需要恢复文件的地址的textbox
cn.Execute s_restore
cn.BeginTrans
If Err.Number = 0 Then
cn.CommitTrans
MsgBox "数据恢复成功!", vbInformation, "提示"
Command1.Enabled = True
Label1.Visible = False
Else
cn.RollbackTrans
MsgBox "数据恢复失败!", vbCritical, "提示"
Command1.Enabled = True
End If
cn.Close
Set cn = Nothing
Me.MousePointer = 1
Else
Exit Sub
End If '''''''''''''''''''''''''''''''''''''''''
On Error Resume Next
Dim DBC As New DataBaseConnection
If db.State = 1 Then
db.Close
End If
db.ConnectionString = DBC.SqlConnectString(d1, d2, d3)
rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
db.CursorLocation = adUseClient
db.Open
Set cmd.ActiveConnection = db
If Err.Number Then
MsgBox Err.Description, 16 + vbOKOnly, Err.Number
Exit Sub
End If
db.DefaultDatabase = "student1"
If Err.Number Then
MsgBox Err.Description, 16 + vbOKOnly, Err.Number
Exit Sub
End If
End If
End Sub '''''''''''''''''''''''''''''''''''''''''''''如果当前没有与要恢复的数据库立连接,则不需要加单引号中的内容。如果希望恢复数据库之后继续建立连接,则需要写这部分代码。我要恢复数据库名称为student1,备份数据库的时候是在连接状态下进行的,但是恢复数据库不可以在数据库存在连接的状态下进行操作!代码的解决方法是:先连接SQL Server中主库master 库,在该库中的sysprocesses表中存放着所有连接到此数据库的连接信息,将这些连接信息用Kill语句删除。然后再恢复数据库student1,由于用Kill语句后,数据库已经被断开,所以在恢复完成后,再用系统最初的连接数据库代码连接上数据库student1。
bakfilename = Text1.Text
If Me.Caption = "备份数据库" Then
On Error GoTo ss
If Len(Dir(bakfilename)) > 0 Then
If MsgBox("此文件已存在,是否替换?", 1 + 32, "询问窗口") = 1 Then
Kill bakfilename '删除已存在同名文件
Else
Text1.Text = ""
Text1.SetFocus
Exit Sub
End If
End If
SqlStr = "BACKUP DATABASE ht TO DISK='" + Trim(Text1.Text) + "'" '备份语句
conn.Execute SqlStr
MsgBox "数据库已成功备份!", 0 + 48, "提示"
Exit Sub
ss:
MsgBox Err.Description
Unload Me
Else
Dim conn1 As New ADODB.Connection
On Error GoTo xx
If MsgBox("恢复前请先作好备份,确定恢复吗?", 1 + 32, "询问提示") = 1 Then
Else
Unload Me
Exit Sub
End If
conn.Close
SqlStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=."
conn1.Open SqlStr
SqlStr = "select spid from master..sysprocesses where dbid=db_id('ht')"
Rsbl1.Open SqlStr, conn1, adOpenKeyset, adLockReadOnly
While Rsbl1.EOF = False '断开所有与数据库连接的设备
SqlStr = "kill " & Rsbl1(0)
conn1.Execute SqlStr
Rsbl1.MoveNext
Wend
Rsbl1.Close
conn1.Close
SqlStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=master;Data Source=."
conn.ConnectionString = SqlStr
conn.Open
SqlStr = "RESTORE DATABASE ht FROM DISK='" + Trim(Text1.Text) + "' WITH REPLACE" '开始恢复
conn.Execute SqlStr
conn.Close
SqlStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=ht;Data Source=ht"
conn.ConnectionString = SqlStr '重新连接数据库
conn.Open
MsgBox "数据库已恢复成功!", 0 + 48, "提示"
Unload Me
Exit Sub
xx:
MsgBox Err.Description
conn.Close
SqlStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=ht;Data Source=."
conn.ConnectionString = SqlStr '重新连接数据库
conn.Open
Unload Me
End If
但就是少了个进度条,希望你能加上~~~