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

本文章介绍了关于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 @smterver 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点，执行的备份语句和发送邮件的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)    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