VB备份与还原SQL数据库代码

Private Sub mnuDBBackUp_Click() '------------------备份数据库

Dim cnbak As New ADODB.Connection

If cnbak.State <> 0 Then cnbak.Close

cnbak.Open "provider=sqloledb;server=" & strSQLServer & ";user id=" & strSQLUser & ";password=" & strSQLPW

Dim backupSQL As String

backupSQL = "USE master " + Chr(10) + Chr(13)

backupSQL = backupSQL & "exec sp_addumpdevice 'disk','bak1'," & "'" & App.Path & "/bak1.bak" & "'"'bak1为备份设备

backupSQL = backupSQL + Chr(10) + Chr(13) + "BACKUP DATABASE SuperMarketdb TO bak1 WITH INIT"

cnbak.Execute backupSQL

MsgBox "数据库备份成功!", vbInformation

cnbak.Close

Set cnbak = Nothing

End Sub

Private Sub mnuDBResume_Click() '-------------------还原数据库

Dim cn As New ADODB.Connection, rs As New ADODB.Recordset

If Dir(App.Path & "/bak1.bak") <> vbNullString Then

If cnMain.State <> 0 Then cnMain.Close

  cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & strSQLUser & ";Password=" & strSQLPW & ";Initial Catalog=master;Data Source=" & strSQLServer      '借助master数据库来恢复strSQLUser是SQL登录名，Password是密码，strSQLServer是服务器名

          cn.CursorLocation = adUseClient

          cn.Open

          rs.Open "select spid from sysprocesses where dbid=db_id('SuperMarketdb')", cn

          Do While Not rs.EOF

              cn.Execute "kill " & rs("spid")             '杀掉sql服务器的所有连接，否则会出现：数据库正在使用，无法完成排它操作等等，很重要

              rs.MoveNext

          Loop

          cn.Execute ("restore database SuperMarketdb from  disk='" & App.Path & "/bak1.bak" & "'  with replace")

          cn.Close

      cnMain.Open "Provider=SQLOLEDB.1;User ID=" & strSQLUser & ";Password=" & strSQLPW & ";Initial Catalog=master;Data Source=" & strSQLServer

      MsgBox "数据库恢复成功!", vbInformation

Else

MsgBox "请先备份数据库！", vbInformation, "提示:"

End If

Set cn = Nothing

End Sub