备份与恢复数据库

备份与恢复数据库

用VB实现对数据库的备份与恢复.下面是一段代码。同时参考了邹建大虾的

'*************************************************************************
'**函 数 名：BackupDatabase
'**描    述：备份数据库,返回出错信息,正常恢复,返回""
'**调    用：BackupDatabase "备份文件名","数据库名"
'**参数说明：
'**          sBackUpfileName  恢复后的数据库存放目录
'**          sDataBaseName    备份的数据名
'**          sIsAddBackup     是否追加到备份文件中
'**说    明：引用Microsoft ActiveX Data Objects 2.x Library
************************************************************
Public Function BackupDatabase(ByVal sBackUpfileName$ _
                                , ByVal sDataBaseName$ _
                                , Optional ByVal sIsAddBackup As Boolean = False _
                                ) As Boolean
                               
 
    Dim My_conn As New ADODB.Connection
 
    Dim ls_conn As String
    Dim Ls_sql As String
    Dim lb_bl_Return As Boolean
   
    On Error GoTo My_Err
   
    ls_conn = gs_str_conn
    My_conn.Open ls_conn
   
    '生成数据库备份语句
    Ls_sql = "backup database [" & sDataBaseName & "]" & vbCrLf & _
            "to disk='" & sBackUpfileName & "'" & vbCrLf & _
            "with description='" & "zj-backup at:" & Date & "(" & Time & ")'" & vbCrLf & _
            IIf(sIsAddBackup, "", ",init")
           
    My_conn.Execute Ls_sql
    lb_bl_Return = True
    BackupDatabase = lb_bl_Return
    Exit Function
My_Err:
    MsgBox Error, vbInformation + vbOKOnly, "系统提示"
    lb_bl_Return = False
   
End Function
 

'*************************************************************************
'**函 数 名：
'-------------- restoredatabase
'**描    述：
'-------------- 恢复数据库,返回出错信息,正常恢复,返回""
'**调    用：
'-------------- restoredatabase "备份文件名","数据库名"
'**参数说明：
'**          sDataBasePath
'------------------------ 恢复后的数据库存放目录
'**          sBackupNumber
'----------------------   是从那个备份号恢复
'**          sReplaceExist
'-----------------------  指定是否覆盖已经存在的数据
'**说    明：
'-----------------------   引用Microsoft ActiveX Data Objects 2.x Library
'*************************************************************************
Public Function RestoreDatabase(ByVal sBackUpfileName$ _
                                , ByVal sDataBaseName$ _
                                , Optional ByVal sDataBasePath$ = "" _
                                , Optional ByVal sBackupNumber& = 1 _
                                , Optional ByVal sReplaceExist As Boolean = False _
                                ) As Boolean
   
        Dim My_conn As ADODB.Connection
        Dim My_record As ADODB.Recordset
       
        Dim iReturn As String
        Dim iI As Integer
        Dim ls_conn As String
        Dim Ls_sql As String
        Dim lb_return As Boolean
       
   On Error GoTo My_Err
            Set My_conn = New ADODB.Connection
            Set My_record = New ADODB.Recordset
           
            '连接数据库服务器,根据你的情况修改连接字符串 连接到 master 库
            ls_conn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=."
            My_conn.Open ls_conn
           
            '得到还原后的数据库存放目录,如果没有指定,存放到SQL SERVER的DATA目录
            If sDataBasePath = "" Then
                Ls_sql = "select filename from master..sysfiles"
                My_record.Open Ls_sql, My_conn, adOpenKeyset, adLockReadOnly
                Ls_sql = My_record(0)
                My_record.Close
                sDataBasePath = Left(Ls_sql, InStrRev(Ls_sql, "/"))
            End If
           
            '检查数据库是否存在
            If sReplaceExist = False Then
                Ls_sql = "select 1 from master..sysdatabases  where name='" & sDataBaseName & "'"
                My_record.Open Ls_sql, My_conn, adOpenKeyset, adLockReadOnly
               
                If My_record.EOF = False Then
                    MsgBox "数据库已经存在!", vbInformation + vbOKOnly, "系统提示"
                    My_record.Close
                End If
               
                My_record.Close
            End If
           
           
            '关闭用户进程,防止其它用户正在使用数据库,导致数据恢复失败
       
            Ls_sql = "select spid from master..sysprocesses where dbid=db_id('" & sDataBaseName & "')"
            My_record.Open Ls_sql, My_conn, adOpenKeyset, adLockReadOnly
       
            While My_record.EOF = False
                Ls_sql = "kill " & My_record(0)
                My_conn.Execute Ls_sql
                My_record.MoveNext
            Wend
       
            My_record.Close
           
            '获取数据库恢复信息
            Ls_sql = "restore filelistonly from disk='" & sBackUpfileName & "'" & vbCrLf & _
                "with file=" & sBackupNumber
            My_record.Open Ls_sql, My_conn, adOpenKeyset, adLockReadOnly
           
            '生成数据库恢复语句
            Ls_sql = "restore database [" & sDataBaseName & "]" & vbCrLf & _
                "from disk='" & sBackUpfileName & "'" & vbCrLf & _
                "with file=" & sBackupNumber & vbCrLf
               
            With My_record
           
                While Not .EOF
                    iReturn = My_record("PhysicalName")
                    iI = InStrRev(iReturn, ".")
                    iReturn = IIf(iI = 0, "", Mid(iReturn, iI)) & "'"
                    Ls_sql = Ls_sql & ",move '" & My_record("LogicalName") & _
                            "' to '" & sDataBasePath & sDataBaseName & iReturn & vbCrLf
                    .MoveNext
                Wend
               
                .Close
            End With
            Ls_sql = Ls_sql & IIf(sReplaceExist, ",replace", "")
           
            My_conn.Execute Ls_sql
            lb_return = True
            RestoreDatabase = lb_return
           
            Exit Function
My_Err:
      MsgBox Error, vbInformation + vbOKOnly, "系统提示"
End Function