SQL Server Express 2005 實現自動備份：Windows 排程任務 + sqlcmd 方案

背景痛點

SQL Server Express 版無 SQL Server Agent，無法使用內建維護計畫排程備份。本文提供零成本、原生工具的自動備份方案：Windows 排程任務 + sqlcmd + 預存程序。

實作步驟

1. 建立備份預存程序 (部署於 master 資料庫)

CREATE PROCEDURE dbo.sp_BackupDatabase
    @databaseName SYSNAME,
    @backupType CHAR(1)  -- F=完整, D=差異, L=記錄
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sqlCommand NVARCHAR(1000), @dateTime NVARCHAR(20);

    -- 產生時間戳：yyyyMMddHHmmss
    SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(), 111), '/', '')
                     + REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '');

    IF @backupType = 'F'
        SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
                          ' TO DISK = ''C:\Backup\' + @databaseName + '_Full_' + @dateTime + '.BAK''';
    ELSE IF @backupType = 'D'
        SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
                          ' TO DISK = ''C:\Backup\' + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL';
    ELSE IF @backupType = 'L'
        SET @sqlCommand = 'BACKUP LOG ' + @databaseName +
                          ' TO DISK = ''C:\Backup\' + @databaseName + '_Log_' + @dateTime + '.TRN''';

    EXECUTE sp_executesql @sqlCommand;
END; GO

2. 建立 SQL 指令檔

路徑：E:\Backup\Backup.sql（可自訂）

EXEC sp_BackupDatabase 'YourDatabaseName', 'F';  -- 每日完整備份
GO

3. 建立 Windows 排程任務

1. 控制台 → 系統管理工具 → 排程任務 → 新增排程任務 → 精靈。
2. 選擇程式：sqlcmd.exe（位於 x:\Program Files\Microsoft SQL Server\90\Tools\Binn\）。
3. 執行頻率：如每日上午 9:00。
4. 輸入具備權限的 Windows 帳號密碼。
5. 完成前勾選 「完成時開啟此任務的進階屬性」。
6. 在「執行」欄修改為完整命令列：
   sqlcmd -S .\SQLEXPRESS -E -i E:\Backup\Backup.sql
   
   • -S .\SQLEXPRESS：本機具名實例（視實際實例名調整）。
   • -E：Windows 整合驗證（排程帳號需具備 sysadmin 或備份權限）。
   • -i：指定輸入 SQL 腳本檔。

4. 驗證備份產出

執行排程後，檢查 C:\Backup\ 目錄是否產生 .BAK / .TRN 檔案。

優化建議

• 建立子目錄按日期分類，避免單一資料夾檔案過多。
• 搭配 xp_cmdshell 或 PowerShell 腳本實現「保留最近 N 天、自動刪除舊備份」。
• 定期執行 RESTORE VERIFYONLY 驗證備份可用性。
• 若為生產環境，建議將備份檔異地複製（網路磁碟、雲端儲存）。

作者：RyanDing | 出處：https://www.cnblogs.com/ryanding/ | 本文版權歸作者與博客園共有，轉載請保留聲明並附原文連結。