SQL2000自動備份數據庫并發送郵件報告數據庫自動備份情況

2024-07-21 02:45:17

字體：大中小

來源：轉載

供稿：網友

最近在一個做企業的一個內部系統，數據庫采用的是SQL2000，為了保證數據的安全性，需要每天下班之后做數據備份，并且通過郵件的方式通知管理員備份情況。備份數據庫很簡單，用SQL代理建立一個作業，每天定時備份數據庫即可，通過SQL2000來發郵件的話，在網上找了些資料，發現有多種方式可以采用。
一、通過SQL Mail
SQL Mail 提供了一種從 Microsoft SQL Server 發送和閱讀電子郵件的簡單方法。原理是通過調用服務器上面的 MAPI 子系統來進行郵件發送，所以服務器上面需要安裝諸如 Microsoft Outlook（不能是Outlook ExPRess）之類的 MAPI 客戶端，而且在發送郵件的時候，Outlook必須處于打開的狀態。具體的設置方法可以通過網上查詢。
二、使用CDONTS
通過調用本機的SMTP服務來發送郵件，所以服務器上必須安裝IIS和SMTP。相應的存儲過程為

CREATE PROCEDURE [dbo].[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
Declare @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

調用方法：
exec sp_send_cdontsmail 'someone@shouji138.com','someone2@hks8.com','測試郵件標題','這里是郵件內容，推薦一個好的小說站，好看書吧，http://www.hks8.com'

三、使用CDOSYS
微軟已經在 Windows 2000、Windows xp 以及 Windows 2003 中淘汰了 CDONTS，所以使用CDOSYS是目前最好的解決方案。使用CDOSYS可以使用遠程的SMTP服務器來發送郵件，我們通過測試163網易的免費郵箱，可以正常發送郵件，相應的存儲過程如下：
CREATE PROCEDURE sys_sendmail @To varchar(100) , @Bcc varchar(500), @Subject varchar(400)=" ",
@Body varchar(4000) =" "

Declare @smtpserver varchar(50) --SMTP服務器地址
Declare @smtpusername varchar(50) --SMTP服務器用戶名
Declare @smtpuserpassWord varchar(50) --SMTP服務器密碼
set @smtpserver = 'smtp.163.com'
set @smtpusername = 'yourname@163.com' --這里設置成你的163郵箱用戶名
set @smtpuserpassword = 'password' --這里設置成你的163郵箱密碼
Declare @object int
Declare @hr int

EXEC @hr = sp_OACreate 'CDO.Message', @object OUT

EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @smtpserver

--下面三條語句是smtp驗證，如果服務器需要驗證，則必須要這三句，你需要修改用戶名和密碼
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1'
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value',@smtpusername
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://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
GO

調用存儲過程發送郵件：exec sys_sendmail 'someone@shouji138.com','someone2@hks8.com','測試郵件標題','這里是郵件內容，手機主題，http://www.shouji138.com'

從以上三種方法的優缺點比較來看，我們當然采取第三種方法，不需要在服務器上裝別的組件和程序。我們可以在SQL代理中建立一個作業，調度設為每天下午6點，執行的數據庫備份語句和發送郵件的SQL如下：

declare @dbname varchar(50)
set @dbname = 'dbtest' --設置數據庫名
declare @filename nvarchar(100)
declare @time datetime
set @time = getdate()
set @filename= 'D:/數據庫自動備份/'+@dbname+substring(replace(replace(replace(CONVERT(varchar, @time, 120 ),'-',''),' ',''),':',''),1,14 )+'.bak'
--print @filename
BACKUP DATABASE dbtest TO DISK = @filename WITH NOINIT, NOUNLOAD, NAME = N'BIS_data_backup', NOSKip , STATS = 10, NOFORMAT

--下面獲取備份之后文件的大小
declare @size int
declare @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
--print @sizeM
--郵件內容
declare @content varchar(2000)
set @content='數據庫自動備份成功。
數據庫名：'+@dbname+'
備份文件名：'+@filename+'
備份文件大?。?+convert(varchar,@sizeM)+'M
備份時間：'+CONVERT(varchar, @time, 120 )+'
這是一封系統自動發出的郵件，用來每天報告數據庫自動備份情況，請不要直接回復。'
--print @content
--發送郵件
EXECUTE dbtest.dbo.sys_sendmail 'dba@hks8.com','dba@shouji138.com',
'數據庫自動備份日報',@content
go