使用 VB6 實作 SQL Server 2000 資料庫備份與還原完整指南

備份策略概覽

T-SQL 備份/還原指令

-- 完整備份
BACKUP DATABASE [DB名] TO DISK = '路徑\檔名.bak'
WITH NAME = '備份集名稱', DESCRIPTION = '說明', INIT, STATS = 10;

-- 差異備份
BACKUP DATABASE [DB名] TO DISK = '路徑\檔名.bak'
WITH DIFFERENTIAL, NAME = '差異備份集', NOINIT, STATS = 10;

-- 還原完整備份 (NORECOVERY 留待套用後續備份)
RESTORE DATABASE [DB名] FROM DISK = '路徑\完整備份.bak'
WITH FILE = 1, NORECOVERY, REPLACE, STATS = 10;

-- 還原差異備份
RESTORE DATABASE [DB名] FROM DISK = '路徑\差異備份.bak'
WITH FILE = 1, NORECOVERY, STATS = 10;

-- 還原記錄備份 (最後一組用 RECOVERY 完成)
RESTORE LOG [DB名] FROM DISK = '路徑\記錄備份.trn'
WITH FILE = 1, RECOVERY, STATS = 10;

VB6 實作核心程式碼

1. 連線設定模組

' 模組級變數
Public Cn As ADODB.Connection

' 連線字串組建
Private Sub ConnectDB()
    Set Cn = New ADODB.Connection
    Dim strCon As String
    strCon = "Provider=SQLOLEDB.1;" & _
             "Password=sa;" & _
             "Persist Security Info=True;" & _
             "User ID=sa;" & _
             "Initial Catalog=jtsg;" & _
             "Data Source=" & ServerIP & ";" & _
             "Use Procedure for Prepare=1;" & _
             "Auto Translate=True;" & _
             "Use Encryption for Data=False;" & _
             "Tag with Column Collation When Possible=False"
    Cn.ConnectionString = strCon
    On Error Resume Next
    Cn.Open
    If Cn.Errors.Count > 0 Then
        Dim i As Long
        For i = 0 To Cn.Errors.Count - 1
            If Cn.Errors(i).Number = -2147467259 Then
                MsgBox "網路不通或伺服器未啟動，請確認後再試", vbOKOnly + vbInformation, "提醒"
                Unload Me
                Exit Sub
            End If
        Next i
    End If
    On Error GoTo 0
End Sub

2. 完整備份

Private Sub BackupFull()
    On Error GoTo ErrHandler
    Call ConnectDB
    If Cn.State <> adStateOpen Then Exit Sub

    Dim bakPath As String
    bakPath = App.Path & "\backup\myjtsgfull_" & Format(Now, "yyyymmdd_hhnnss") & ".bak"

    Cn.Execute "BACKUP DATABASE jtsg TO DISK = '" & bakPath & "' " & _
               "WITH NAME = 'jtsg_full_backup', " & _
               "DESCRIPTION = 'Full Backup of jtsg', " & _
               "INIT, STATS = 10"

    MsgBox "完整備份完成：" & bakPath, vbOKOnly + vbInformation, "成功"
    Exit Sub

ErrHandler:
    MsgBox "備份失敗：" & Err.Description, vbCritical, "錯誤"
End Sub

3. 差異備份

Private Sub BackupDifferential()
    On Error GoTo ErrHandler
    Call ConnectDB
    If Cn.State <> adStateOpen Then Exit Sub

    Dim bakPath As String
    bakPath = App.Path & "\backup\myjtsgdiff_" & Format(Now, "yyyymmdd_hhnnss") & ".bak"

    Cn.Execute "BACKUP DATABASE jtsg TO DISK = '" & bakPath & "' " & _
               "WITH DIFFERENTIAL, " & _
               "NAME = 'jtsg_diff_backup', " & _
               "DESCRIPTION = 'Differential Backup of jtsg', " & _
               "NOINIT, STATS = 10"

    MsgBox "差異備份完成：" & bakPath, vbOKOnly + vbInformation, "成功"
    Exit Sub

ErrHandler:
    MsgBox "備份失敗：" & Err.Description, vbCritical, "錯誤"
End Sub

4. 還原資料庫

Private Sub RestoreDatabase(fullBakPath As String, diffBakPath As String, logBakPaths As Collection)
    On Error GoTo ErrHandler
    Call ConnectDB
    If Cn.State <> adStateOpen Then Exit Sub

    ' 1. 還原完整備份 (NORECOVERY)
    Cn.Execute "RESTORE DATABASE jtsg FROM DISK = '" & fullBakPath & "' " & _
               "WITH FILE = 1, NORECOVERY, REPLACE, STATS = 10"

    ' 2. 還原最新差異備份 (若有)
    If diffBakPath <> "" Then
        Cn.Execute "RESTORE DATABASE jtsg FROM DISK = '" & diffBakPath & "' " & _
                   "WITH FILE = 1, NORECOVERY, STATS = 10"
    End If

    ' 3. 依序還原記錄備份 (最後一組 RECOVERY)
    Dim i As Long
    For i = 1 To logBakPaths.Count
        Dim isLast As Boolean
        isLast = (i = logBakPaths.Count)
        Dim recoveryOpt As String
        recoveryOpt = IIf(isLast, "RECOVERY", "NORECOVERY")
        Cn.Execute "RESTORE LOG jtsg FROM DISK = '" & logBakPaths(i) & "' " & _
                   "WITH FILE = 1, " & recoveryOpt & ", STATS = 10"
    Next i

    MsgBox "資料庫還原完成", vbOKOnly + vbInformation, "成功"
    Exit Sub

ErrHandler:
    MsgBox "還原失敗：" & Err.Description, vbCritical, "錯誤"
    ' 嘗試強制恢復資料庫線上狀態
    On Error Resume Next
    Cn.Execute "RESTORE DATABASE jtsg WITH RECOVERY"
End Sub

關鍵參數說明

• FILE = n：備份檔內第 n 個備份集（同一檔案可追加多組備份）。
• NORECOVERY：還原後保持恢復中狀態，允許套用後續差異/記錄備份。
• RECOVERY：完成恢復，回滾未完成交易，資料庫上線可用（預設）。
• REPLACE：覆蓋現有資料庫（還原至不同機器或重建時需加上）。
• STATS = 10：每 10% 顯示進度，便於監控長時間作業。

實務建議

1. 備份檔案管理：採用日期時間命名、分資料夾存放、定期複製至異地/雲端。
2. 驗證備份可用性：定期在測試環境執行 RESTORE VERIFYONLY 或實際還原演練。
3. 維護計畫自動化：生產環境建議使用 SQL Server Agent 維護計畫或 Ola Hallengren 腳本，而非依賴前端 VB 程式排程。
4. 權限最小化：備份/還原操作需 db_backupoperator / db_owner 或 sysadmin，前端應用避免使用 sa 直連。