查看内容

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

  • 2020-03-24 13:12
  • 数据库知识
  • Views

本文章介绍了关于sqlserver自动备份数据库且邮箱发送邮箱状态,有需要让机器自动备份数据库的朋友可以看看本文章的做法。

一、通过SQL Mail SQL Mail 提供了一种从 Microsoft SQL Server 发送和阅读电子邮件的简单方法。原理是通过调用服务器上面的 MAPI 子系统来进行邮件发送,所以服务器上面需要安装诸如 Microsoft Outlook 之类的 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) = nullASDeclare @MailID intDeclare @hr intEXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUTEXEC @hr = sp_OASetProperty @MailID, 'From',@FromEXEC @hr = sp_OASetProperty @MailID, 'Body', @BodyEXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCCEXEC @hr = sp_OASetProperty @MailID, 'CC', @CCEXEC @hr = sp_OASetProperty @MailID, 'Subject', @SubjectEXEC @hr = sp_OASetProperty @MailID, 'To', @ToEXEC @hr = sp_OAMethod @MailID, 'Send', NULLEXEC @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().Value','2' EXEC @hr = sp_OASetProperty @object, 'Configuration.fields().Value', @smtpserver

--下面三条语句是smtp验证,如果服务器需要验证,则必须要这三句,你需要修改用户名和密码EXEC @hr = sp_OASetProperty @object, 'Configuration.fields().Value','1' EXEC @hr = sp_OASetProperty @object, 'Configuration.fields().Value',@smtpusernameEXEC @hr = sp_OASetProperty @object, 'Configuration.fields().Value',@smtpuserpassword

EXEC @hr = sp_OAMethod @object, 'Configuration.Fields.Update', nullEXEC @hr = sp_OASetProperty @object, 'To', @ToEXEC @hr = sp_OASetProperty @object, 'Bcc', @BccEXEC @hr = sp_OASetProperty @object, 'From', @smtpusernameEXEC @hr = sp_OASetProperty @object, 'Subject', @Subject

EXEC @hr = sp_OASetProperty @object, 'TextBody', @BodyEXEC @hr = sp_OAMethod @object, 'Send', NULL

--判断出错IF @hr 0BEGIN EXEC sp_OAGetErrorInfo @object print 'failed' return @objectENDPRINT 'success'EXEC @hr = sp_OADestroy @objectGO

调用存储过程发送邮件: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 datetimeset @time = getdate()set @filename= 'D:数据库自动备份'+@dbname+substring(replace(replace(replace(CONVERT(varchar, @time, 120 ),'-',''),' ',''),':',''),1,14 )+'.bak'--print @filenameBACKUP DATABASE dbtest TO DISK = @filename WITH NOINIT, NOUNLOAD, NAME = N'BIS_data_backup', NOSKIP , STATS = 10, NOFORMAT

--下面获取备份之后文件的大小declare @size intdeclare @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','数据库自动备份日报',@contentgo