需求背景

企業內部系統使用 SQL Server 2000，需每日下班後自動備份數據庫，並通過郵件發送備份狀態（文件名、大小、耗時）給 DBA。

三種郵件發送方案對比

方案	原理	依賴	優缺點
SQL Mail	調用服務器 MAPI 子系統	Outlook 完整版（非 Express），需保持打開	配置複雜、穩定性差、微軟已棄用
CDONTS	調用本機 IIS SMTP 服務	IIS + SMTP 服務	僅支持本機 SMTP，Windows 2000/XP/2003 已棄用
CDOSYS (推薦)	調用 CDO.Message COM 組件，支持遠程 SMTP 認證	Windows 2000+ 內置，無需額外安裝	支持遠程 SMTP（如 163、Gmail）、SSL/TLS、HTML 正文、附件，現代首選

CDOSYS 存儲過程實現（核心代碼）

CREATE PROCEDURE sys_sendmail
    @To varchar(100),
    @Bcc varchar(500),
    @Subject varchar(400) = '',
    @Body varchar(4000) = ''
AS
BEGIN
    DECLARE @smtpserver varchar(50) = 'smtp.163.com'
    DECLARE @smtpusername varchar(50) = 'yourname@163.com'
    DECLARE @smtpuserpassword varchar(50) = 'yourpassword'
    DECLARE @obj int, @hr int

    EXEC @hr = sp_OACreate 'CDO.Message', @obj OUT
    EXEC @hr = sp_OASetProperty @obj, 'Configuration.fields("https://schemas.microsoft.com/cdo/configuration/sendusing").Value', '2'
    EXEC @hr = sp_OASetProperty @obj, 'Configuration.fields("https://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @smtpserver
    -- SMTP 認證
    EXEC @hr = sp_OASetProperty @obj, 'Configuration.fields("https://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value', '1'
    EXEC @hr = sp_OASetProperty @obj, 'Configuration.fields("https://schemas.microsoft.com/cdo/configuration/sendusername").Value', @smtpusername
    EXEC @hr = sp_OASetProperty @obj, 'Configuration.fields("https://schemas.microsoft.com/cdo/configuration/sendpassword").Value', @smtpuserpassword
    EXEC @hr = sp_OAMethod @obj, 'Configuration.Fields.Update', NULL

    EXEC @hr = sp_OASetProperty @obj, 'To', @To
    EXEC @hr = sp_OASetProperty @obj, 'Bcc', @Bcc
    EXEC @hr = sp_OASetProperty @obj, 'From', @smtpusername
    EXEC @hr = sp_OASetProperty @obj, 'Subject', @Subject
    EXEC @hr = sp_OASetProperty @obj, 'TextBody', @Body
    EXEC @hr = sp_OAMethod @obj, 'Send', NULL

    IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @obj; PRINT 'failed'; RETURN @obj END
    PRINT 'success'
    EXEC @hr = sp_OADestroy @obj
END

SQL Server Agent 作業完整腳本

DECLARE @dbname varchar(50) = 'dbtest'
DECLARE @filename nvarchar(100), @time datetime = GETDATE()
SET @filename = 'D:\DB_Backup\' + @dbname + REPLACE(REPLACE(REPLACE(CONVERT(varchar, @time, 120), '-', ''), ' ', ''), ':', '') + '.bak'

BACKUP DATABASE @dbname TO DISK = @filename WITH NOINIT, NOUNLOAD, NAME = N'Daily_Backup', NOSKIP, STATS = 10, NOFORMAT

-- 獲取最新備份文件大小（MB）
DECLARE @size int, @sizeM decimal(5,2)
SELECT TOP 1 @size = backup_size FROM msdb.dbo.backupset WHERE database_name = @dbname ORDER BY backup_start_date DESC
SET @sizeM = CAST(@size AS float) / 1024 / 1024

-- 構建郵件內容
DECLARE @content varchar(2000) = '數據庫自動備份成功。' + CHAR(13) + CHAR(10) +
    '數據庫名：' + @dbname + CHAR(13) + CHAR(10) +
    '備份文件：' + @filename + CHAR(13) + CHAR(10) +
    '文件大小：' + CAST(@sizeM AS varchar) + ' MB' + CHAR(13) + CHAR(10) +
    '備份時間：' + CONVERT(varchar, @time, 120) + CHAR(13) + CHAR(10) +
    '此為系統自動發送，請勿直接回覆。'

-- 發送郵件
EXEC sys_sendmail 'dba@company.com', 'backup-log@company.com', '【每日】數據庫備份報告', @content

部署步驟

1. 在目標數據庫創建 sys_sendmail 存儲過程。
2. SQL Server Agent → 新建作業 → 步驟填入上述備份+發送腳本。
3. 排程設置：每日 18:00 執行（或業務低峰期）。
4. 啟用 Agent 服務（自動啟動類型）。
5. 測試：手動執行作業，檢查備份文件與郵件接收。

避坑指南

• SQL Server 服務賬號需有備份目錄寫入權限。
• SMTP 服務器需允許該 IP 發信（163 等需開啟 SMTP 服務並獲取授權碼）。
• sp_OA* 存儲過程需啟用 Ole Automation Procedures：
  EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;
• 備份文件定期清理策略（如保留 7 天）避免磁盤佔滿。