您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

SQL Server 2000自動備份與郵件通知實戰：CDOSYS方案最佳實踐

編輯：MySQL 資料庫相關知識

企業級資料庫自動化備份需求背景

內部系統採用SQL Server 2000，需每日下班後自動備份並透過郵件通知管理員備份狀態。備份本身透過SQL Agent作業排程簡單實現，關鍵在於可靠的郵件發送機制選擇。

三種郵件發送方案深度比較

方案一：SQL Mail（MAPI架構）

原理：呼叫伺服器MAPI子系統發送郵件
硬性需求：伺服器必須安裝Microsoft Outlook（非Outlook Express），且Outlook需處於開啟狀態
缺點：依賴桌面郵件客戶端，伺服器環境不穩定，不適合無人值守生產環境

方案二：CDONTS（本地SMTP服務）

原理：調用本機SMTP服務發送郵件
需求：伺服器安裝IIS與SMTP服務

存儲過程範例：

CREATE PROCEDURE sp_send_cdontsmail
@From varchar(100), @To varchar(100), @Subject varchar(100),
@Body varchar(4000), @CC varchar(100)=null, @BCC varchar(100)=null
AS
DECLARE @MailID int, @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From', @From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC', @BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID

缺點：Windows 2000/XP/2003已淘汰CDONTS，不再推薦使用

方案三：CDOSYS（推薦方案）⭐

優勢：支援遠端SMTP伺服器（如163、Gmail）、無需本機安裝額外組件、現代Windows版本原生支援

核心存儲過程：

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

EXEC @hr = sp_OACreate 'CDO.Message', @object OUT
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("https://schemas.microsoft.com/cdo/configuration/sendusing").Value', '2'
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("https://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @smtpserver
-- SMTP認證三部曲
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("https://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value', '1'
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("https://schemas.microsoft.com/cdo/configuration/sendusername").Value', @smtpusername
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("https://schemas.microsoft.com/cdo/configuration/sendpassword").Value', @smtpuserpassword
EXEC @hr = sp_OAMethod @object, 'Configuration.Fields.Update', NULL
EXEC @hr = sp_OASetProperty @object, 'To', @To
EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc
EXEC @hr = sp_OASetProperty @object, 'From', @smtpusername
EXEC @hr = sp_OASetProperty @object, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @object, 'TextBody', @Body
EXEC @hr = sp_OAMethod @object, 'Send', NULL

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

完整自動化備份與通知作業腳本

-- SQL Agent作業步驟：每日18:00執行
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 = 'Daily_Backup', NOSKIP, STATS = 10, NOFORMAT

-- 取得最新備份檔案大小
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) = 
'資料庫自動備份成功。\n' +
'資料庫名：' + @dbname + '\n' +
'備份檔案：' + @filename + '\n' +
'檔案大小：' + CAST(@sizeM AS varchar) + ' MB\n' +
'備份時間：' + CONVERT(varchar, @time, 120) + '\n' +
'此為系統自動發送，請勿直接回覆。'

EXEC sys_sendmail 'dba@company.com', 'backup-log@company.com', 
'資料庫每日備份報告', @content