VB实现数据库的备份还原.

VB实现SQL SERVER2000数据库备份还原

********************************************************* '* 名称：BackupDatabase '* 功能：备份数据库 '* 控件：一个文本框和两个按钮（备份到和确定） '*DataBaseConnection：数据库连接对象 '*d1:主机名（服务器名） '*d2:帐号'*d3：密码 '********************************************************* 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 mitTrans 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

mitTrans 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