本文提供一個 SQL Server 2005 自動備份腳本，採「永遠只保留最新一份」覆蓋模式，適用於測試環境、開發機、或備份儲存空間有限的場景。腳本包含：截斷記錄檔、收縮資料庫、動態路徑建構、備份裝置管理、完整備份執行。
腳本完整程式碼
DECLARE @DBName VARCHAR(200);
SET @DBName = 'ReportServer$SQL2005'; -- 修改為目標資料庫名稱
-- 1. 截斷交易記錄檔 (簡單復原模式下等同 CHECKPOINT)
DUMP TRANSACTION @DBName WITH NO_LOG;
-- 注意：SQL 2008+ 已移除 DUMP TRANSACTION，改用 ALTER DATABASE ... SET RECOVERY SIMPLE + CHECKPOINT
-- 2. 收縮資料庫釋放空間
DBCC SHRINKDATABASE (@DBName, TRUNCATEONLY);
-- 3. 設定備份路徑參數
USE master;
DECLARE @Version VARCHAR(20), @BasePath VARCHAR(200), @BakPath VARCHAR(200);
SET @BasePath = 'F:\tmp'; -- 備份根目錄 (需 SQL 服務帳戶寫入權限)
SET @Version = 'V6.1'; -- 版本子目錄
SET @BakPath = @BasePath + '\' + @Version + '\Db.Bak'; -- 完整備份檔路徑
-- 4. 管理備份裝置 (存在則刪除實體檔與邏輯裝置)
IF EXISTS (SELECT * FROM sys.backup_devices WHERE name = 'CTOS_DB_Bak')
BEGIN
EXEC sp_dropdevice 'CTOS_DB_Bak';
-- 刪除實體備份檔 (需啟用 xp_cmdshell)
DECLARE @tmpcmd VARCHAR(100);
SET @tmpcmd = 'del ' + @BakPath;
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
EXEC master..xp_cmdshell @tmpcmd;
EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;
END
-- 5. 建立備份裝置並執行完整備份
EXEC sp_addumpdevice 'disk', 'CTOS_DB_Bak', @BakPath;
BACKUP DATABASE @DBName TO CTOS_DB_Bak;
關鍵參數說明
| 參數 | 用途 | 修改建議 |
|---|
| @DBName | 目標資料庫名稱 | 必修改 |
| @BasePath | 備份根目錄 | 確保磁碟空間、SQL 服務帳戶有寫入權限 |
| @Version | 版本子目錄 | 可改為日期格式 CONVERT(VARCHAR(10), GETDATE(), 112) 實現每日保留 |
排程部署方式
- 另存為
AutoBackup.sql
- SQL Server Agent → 作業 → 新增作業
- 步驟：類型 Transact-SQL 指令碼，資料庫 master，命令輸入腳本內容
- 排程：每日/每週/自訂
- 通知：失敗寄信給 DBA
- 或使用
sqlcmd -S . -i AutoBackup.sql 搭配 Windows 排程器
進階改良方向 (生產環境建議)
- 每日保留多份：檔名加入日期
Db_YYYYMMDD.bak，配合清理舊檔邏輯
- 差異/記錄備份：完整備份 + 定時差異備份 + 高頻記錄備份 (RPO/RTO 需求)
- 壓縮備份：SQL 2008+
WITH COMPRESSION，2005 需第三方工具
- 加密備份：SQL 2014+
WITH ENCRYPTION
- 驗證備份：
RESTORE VERIFYONLY FROM DISK = ...
- 異地複製：備份完成後複製至遠端/雲端儲存
- 監控告警：備份失敗、耗時異常、檔案大小異常觸發告警
版本相容性注意
| 指令 | SQL 2005 | SQL 2008+ |
|---|
| DUMP TRANSACTION ... NO_LOG | 支援 | 已移除，改用 ALTER DATABASE ... SET RECOVERY SIMPLE; CHECKPOINT; |
| sp_addumpdevice | 支援 | 支援 (但建議直接 BACKUP TO DISK=...) |
| xp_cmdshell | 預設關閉 | 預設關閉 |
安全性提醒
xp_cmdshell 極高風險，僅在受控環境短暫啟用，用畢立即關閉- 生產環境建議改用
BACKUP DATABASE ... TO DISK = @path WITH INIT, FORMAT 直接指定檔案路徑，無需備份裝置與 xp_cmdshell
- 備份檔含敏感資料，檔案層級需加密 (EFS/BitLocker/第三方)
現代化寫法參考 (SQL 2008+ 推薦)
DECLARE @DBName SYSNAME = 'MyDB';
DECLARE @Path NVARCHAR(500) = 'F:\Backup\MyDB_' + CONVERT(CHAR(8), GETDATE(), 112) + '.bak';
BACKUP DATABASE @DBName
TO DISK = @Path
WITH INIT, COMPRESSION, CHECKSUM, STATS = 10;
