[大数据]VB6.0超级模块的封转之数据库还原和备份--来自VB6写的广东拉链厂生成销售系统

Public cnn As New ADODB.Connection
Public gblServer      As String
Public gblDataBase    As String
Public gblServerLogID As String
Public gblServerPwd   As String
Public gblRemoteServerIP As String '远程服务器地址

Public SQL2008还原数据库专用的字符串 As String
Public Sub 连接数据库SQL2008() 'cnn

  
   gblServerLogID = "sa"
   gblServerPwd = "1111"
   gblDataBase = "BoYa"
   gblServer = "127.0.0.1,1433"   '还要打开本地端口1433
  
'SQL2008 = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & gblServerLogID & _
'                                                                         ";pwd=" & gblServerPwd & _
'                                                                         ";Initial Catalog=" & gblDataBase & _
'                                                                         ";Data Source=" & gblServer
'
                                                                         
     
SQL2008还原数据库专用的字符串 = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & gblServerLogID & _
                                                                         ";Password=" & gblServerPwd & _
                                                                           ";Initial Catalog=" & "master" & _
                                                                         ";Data Source=" & gblServer
                                                                         
'
'   cnn.Open SQL2008
End Sub





 Sub 备份数据库(ByVal 数据库名称 As String, ByVal 系统数据库连接字符串 As String, ByVal 数据库备份文件的路径 As String) '------------------备份数据库
            Dim cnbak As New ADODB.Connection
            If cnbak.State <> 0 Then cnbak.Close
            cnbak.Open 系统数据库连接字符串
            Dim backupSQL As String
            backupSQL = "backup database " & 数据库名称 & " to disk='" & 数据库备份文件的路径 & "'   with   name='" & 数据库名称 & " backup all',description='Full Backup Of " & 数据库名称 & "'"

            cnbak.Execute backupSQL
            MsgBox "数据库备份成功!", vbInformation
            cnbak.Close
            Set cnbak = Nothing
End Sub


 Sub 还原数据库(ByVal 数据库名称 As String, ByVal 系统数据库连接字符串 As String, ByVal 数据库备份文件的路径 As String)
                Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
                If Dir(数据库备份文件的路径) <> vbNullString Then
                        cn.CursorLocation = adUseClient
                         cn.Open 系统数据库连接字符串
                         
                          rs.Open "select spid from master..sysprocesses where dbid=db_id('" & 数据库名称 & "')", cn, adOpenKeyset, adLockReadOnly
                          Do While Not rs.EOF
                    
                               Form1.List1.AddItem rs("spid")
                               cn.Execute "kill " & rs(0)             '杀掉sql服务器的所有连接，否则会出现：数据库正在使用，无法完成排它操作等等，很重要
                              
                            rs.MoveNext
                          Loop
                          rs.Close
                   
                          cn.Execute ("restore database " & 数据库名称 & " from  disk='" & 数据库备份文件的路径 & "'  with replace")
                          cn.Close
                      MsgBox "数据库恢复成功!", vbInformation
                Else
                       MsgBox "请先备份数据库！", vbInformation, "提示:"
                End If
                
                Set cn = Nothing
End Sub

?

?

窗体的代码如下

Private Sub Command1_Click()
备份数据库 "BoYa", SQL2008还原数据库专用的字符串, App.Path & "\Back_BoYa.bak"
End Sub

Private Sub Command2_Click()
还原数据库 "BoYa", SQL2008还原数据库专用的字符串, App.Path & "\Back_BoYa.bak"
End Sub

Private Sub Form_Load()
连接数据库SQL2008
End Sub

数据库技术交流 qq 1016058890

?