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

上一个：关于VB入门的问题
下一个：C++和VB的区别