sqlserver自动备份数据库且邮箱发送邮箱状态

    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

    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 = '[email protected]'  --这里设置成你的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

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 '[email protected]','[email protected]',
'数据库自动备份日报',@content
go