*********************************************************
'* 名稱：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? ?? ?? ?? ?? ?? ?? ? '''''''''''''''''''''''''''''''''''''''''''''