VB6 結合 ADO 實作 SQL Server 2000 資料庫備份與還原完整模組代碼

模組一：資料庫備份函數 fBackupDatabase_a

' 參數：
' sBackUpfileName - 備份檔完整路徑 (如 "D:\Backup\MyDB.bak")
' sDataBaseName   - 要備份的資料庫名稱
' sIsAddBackup    - True=追加至現有備份檔, False=覆蓋建立新備份集 (預設 False)
' 回傳：空字串=成功, 非空=錯誤訊息

Public Function fBackupDatabase_a(ByVal sBackUpfileName$, _
    ByVal sDataBaseName$, _
    Optional ByVal sIsAddBackup As Boolean = False) As String

    Dim iDb As New ADODB.Connection
    Dim iConcStr$, iSql$, iReturn$
    On Error GoTo lbErr

    ' 連線字串請依實際環境修改 Data Source
    iConcStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=."
    iDb.Open iConcStr

    ' 動態建構 BACKUP 指令
    iSql = "BACKUP DATABASE [" & sDataBaseName & "]" & vbCrLf & _
           "TO DISK = '" & sBackUpfileName & "'" & vbCrLf & _
           "WITH DESCRIPTION = 'Backup at: " & Format(Now, "yyyy-mm-dd hh:nn:ss") & "'" & vbCrLf & _
           IIf(sIsAddBackup, "", ", INIT")  ' INIT 覆蓋, 省略則追加

    iDb.Execute iSql
    iReturn = ""
    GoTo lbExit

lbErr:
    iReturn = Err.Description
lbExit:
    fBackupDatabase_a = iReturn
    If iDb.State = adStateOpen Then iDb.Close
    Set iDb = Nothing
End Function

模組二：資料庫還原函數 fRestoreDatabase_a

' 參數：
' sBackUpfileName - 備份檔完整路徑
' sDataBaseName   - 要還原的目標資料庫名稱
' sDataBasePath   - 資料/記錄檔放置目錄 (空白則用 master 系統預設路徑)
' sBackupNumber   - 備份集編號 (一個 .bak 可含多組備份, 預設 1)
' sReplaceExist   - True=覆蓋同名資料庫, False=存在則報錯 (預設 False)
' 回傳：空字串=成功, 非空=錯誤訊息

Public Function fRestoreDatabase_a(ByVal sBackUpfileName$, _
    ByVal sDataBaseName$, _
    Optional ByVal sDataBasePath$ = "", _
    Optional ByVal sBackupNumber& = 1, _
    Optional ByVal sReplaceExist As Boolean = False) As String

    Dim iDb As New ADODB.Connection, iRe As New ADODB.Recordset
    Dim iConcStr$, iSql$, iReturn$, iI&
    On Error GoTo lbErr

    iConcStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=."
    iDb.Open iConcStr

    ' 1. 若未指定路徑, 取 master 系統資料庫預設資料目錄
    If sDataBasePath = "" Then
        iSql = "SELECT filename FROM master..sysfiles"
        iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
        iSql = iRe(0)
        iRe.Close
        sDataBasePath = Left(iSql, InStrRev(iSql, "\"))
    End If

    ' 2. 檢查目標資料庫是否存在
    If Not sReplaceExist Then
        iSql = "SELECT 1 FROM master..sysdatabases WHERE name = '" & sDataBaseName & "'"
        iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
        If Not iRe.EOF Then
            iReturn = "資料庫已經存在!"
            iRe.Close
            GoTo lbExit
        End If
        iRe.Close
    End If

    ' 3. 強制關閉所有使用該資料庫的連線 (避免還原失敗)
    iSql = "SELECT spid FROM master..sysprocesses WHERE dbid = DB_ID('" & sDataBaseName & "')"
    iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
    Do While Not iRe.EOF
        iDb.Execute "KILL " & iRe(0)
        iRe.MoveNext
    Loop
    iRe.Close

    ' 4. 讀取備份檔內檔案清單, 動態建構 MOVE 子句指定還原路徑
    iSql = "RESTORE FILELISTONLY FROM DISK = '" & sBackUpfileName & "' WITH FILE = " & sBackupNumber
    iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly

    iSql = "RESTORE DATABASE [" & sDataBaseName & "]" & vbCrLf & _
           "FROM DISK = '" & sBackUpfileName & "'" & vbCrLf & _
           "WITH FILE = " & sBackupNumber & vbCrLf

    Do While Not iRe.EOF
        Dim logicalName$, physicalName$, ext$
        logicalName = iRe("LogicalName")
        physicalName = iRe("PhysicalName")
        iI = InStrRev(physicalName, ".")
        ext = IIf(iI = 0, "", Mid(physicalName, iI))  ' 保留副檔名
        iSql = iSql & ", MOVE '" & logicalName & "' TO '" & sDataBasePath & sDataBaseName & ext & "'" & vbCrLf
        iRe.MoveNext
    Loop
    iRe.Close

    iSql = iSql & IIf(sReplaceExist, ", REPLACE", "")
    iDb.Execute iSql
    iReturn = ""
    GoTo lbExit

lbErr:
    iReturn = Err.Description
lbExit:
    fRestoreDatabase_a = iReturn
    If iRe.State = adStateOpen Then iRe.Close
    If iDb.State = adStateOpen Then iDb.Close
    Set iRe = Nothing
    Set iDb = Nothing
End Function

使用範例

' 備份
Dim msg$
msg = fBackupDatabase_a("D:\Backup\Northwind_20240115.bak", "Northwind")
If msg <> "" Then MsgBox "備份失敗: " & msg Else MsgBox "備份成功"

' 還原 (覆蓋模式, 指定檔案目錄)
msg = fRestoreDatabase_a("D:\Backup\Northwind_20240115.bak", "Northwind_Test", _
                         "D:\MSSQL\Data\", 1, True)
If msg <> "" Then MsgBox "還原失敗: " & msg Else MsgBox "還原成功"

關鍵設計亮點

• 動態 MOVE 子句：自動讀取備份檔邏輯檔名，重新映射至指定目錄，支援異機還原、檔案重組。
• 強制 KILL 連線：還原前清除所有佔用目標資料庫的 SPID，避免「資料庫正在使用中」失敗。
• 彈性參數：支援追加備份/覆蓋備份、多備份集選擇、自訂還原路徑、存在即覆蓋/報錯兩種模式。
• 錯誤捕獲：統一回傳錯誤訊息字串，呼叫端易於判斷與記錄。

參考需求

專案需加入參考：Microsoft ActiveX Data Objects 2.x Library (ADO)。