sqlserver自動備份資料庫且郵箱發送郵箱狀態

一、通過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','

測試郵件標題','這裡是郵件內容，推
三、使用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) =" " AS 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','測試郵件標題','這裡是郵件內容，
從以上三種方法的優缺點比較來看，我們當然採取第三種方法，不需要在伺服器上裝別的組件和程式。我們可以在SQL代理中建立一個作業，調度設為每天下午6點，執行的Database Backup語句和發送郵件的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