自动备份数据库并发送邮件报告数据库

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

   最近在一个做企业的一个内部系统,数据库采用的是SQL2000,为了保证数据的安全性,需要每天下班之后做数据备份,并且通过邮件的方式通知管理员备份情况。备份数据库很简单,用SQL代理建立一个作业,每天定时备份数据库即可,通过SQL2000来发邮件的话,在网上找了些资料,发现有多种方式可以采用。

SQL Server发送邮件的存储过程

一、通过SQL Mail SQL Mail 提供了一种从 Microsoft SQL Server
发送和阅读电子邮件的简单方法。原理是通过调用服务器上面的 MAPI
子系统来进行邮件发送,所以服务器上面需要安装诸如 Microsoft Outlook
之类的 MAPI
客户端,而且在发送邮件的时候,Outlook必须处于打开的状态。具体的设置方法可以通过网上查询。二、使用CDONTS
通过调用本机的SMTP服务来发送邮件,所以服务器上必须安装IIS和SMTP。相应的存储过程为

  一、通过SQL Mail

对于DB中的资料变更,有时会有寄Mail通知相关人员的需求。下面是实现这一功能的一种方法

代码如下复制代码

  SQL Mail 提供了一种从 Microsoft SQL Server
发送和阅读电子邮件的简单方法。原理是通过调用服务器上面的 MAPI
子系统来进行邮件发送,所以服务器上面需要安装诸如 Microsoft
Outlook(不能是Outlook ExPRess) 之类的 MAPI
客户端,而且在发送邮件的时候,Outlook必须处于打开的状态。具体的设置方法可以通过网上查询。

1.建立发Mail的存储过程

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

  二、使用CDONTS

CREATE PROCEDURE [dbo].[sp_send_mail]
@From varchar(100) ,
@To varchar(2000) ,
@Subject varchar(2000)=” “,
@Body varchar(4000) =” “,
@BCC varchar(4000) =” “
/******************************************
This stored procedure takes the parameters and sends an e-mail. All the
mail configurations are hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary. References to the CDOSYS
objects are at the following MSDN Web site:

library/en-us/cdosys/html/_cdosys_messaging.asp
*******************************************www.9778.com,/

调用方法:

  通过调用本机的SMTP服务来发送邮件,所以服务器上必须安装IIS和SMTP。相应的存储过程为

AS Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(2000)

代码如下复制代码 exec sp_send_cdontsmail
‘someone@shouji138.com’,’someone2@hks8.com’,’

  CREATE PROCEDURE [dbo].[sp_send_cdontsmail]

–***** Create the CDO.Message Object *****

测试邮件标题’,’这里是邮件内容,推三、使用CDOSYS 微软已经在 Windows
2000、Windows XP 以及 Windows 2003 中淘汰了
CDONTS,所以使用CDOSYS是目前最好的解决方案。使用CDOSYS可以使用远程的SMTP服务器来发送邮件,我们通过测试163网易的免费邮箱,可以正常发送邮件,相应的存储过程如下:

  @From varchar(100),

EXEC @hr = sp_OACreate ‘CDO.Message’, @iMsg OUT

代码如下复制代码

  @To varchar(100),

–*****Configuring the Message Object *****

CREATE PROCEDURE sys_sendmail @To varchar(100) , @Bcc varchar(500),
@Subject varchar(400)= ,@Body varchar(4000) =

  @Subject varchar(100),

— This is to configure a remote SMTP server.
— Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields
(“‘,
‘xxxserver’

AS

  @Body varchar(4000),



EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields
(“‘
EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields
(“‘
EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields
(“‘
EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields
(“‘
EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields
(“‘
EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields
(“‘

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

  @CC varchar(100) = null,

— This is to configure the Server Name or IP address.

EXEC @hr = sp_OACreate ‘CDO.Message’, @object OUT

  @BCC varchar(100) = null

— Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, ‘Configuration.Fields.Update’, null

EXEC @hr = sp_OASetProperty @object, ‘Configuration.fields().Value’,’2′
EXEC @hr = sp_OASetProperty @object, ‘Configuration.fields().Value’,
@smtpserver

  AS

— Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, ‘To’, @To
EXEC @hr = sp_OASetProperty @iMsg, ‘From’, @From
EXEC @hr = sp_OASetProperty @iMsg, ‘Subject’, @Subject
EXEC @hr = sp_OASetProperty @iMsg, ‘Bcc’, @Bcc

–下面三条语句是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

  Declare @MailID int

— If you are using HTML e-mail, use ‘HTMLBody’ instead of ‘TextBody’.
EXEC @hr = sp_OASetProperty @iMsg, ‘HTMLbody’, @Body
–EXEC @hr = sp_OASetProperty @iMsg, ‘TextBody’, @Body
EXEC @hr = sp_OAMethod @iMsg, ‘Send’, NULL

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

  Declare @hr int

— Sample error handling.

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

  EXEC @hr = sp_OACreate ‘CDONTS.NewMail’, @MailID OUT

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ‘ Source: ‘ + @source
PRINT @output
SELECT @output = ‘ Description: ‘ + @description
PRINT @output
END
ELSE
BEGIN
PRINT ‘ sp_OAGetErrorInfo failed.’
RETURN
END
END

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

  EXEC @hr = sp_OASetProperty @MailID, ‘From’,@From

— Do some error handling after each step if you have to.
— Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg

调用存储过程发送邮件:exec sys_sendmail
‘someone@shouji138.com’,’someone2@hks8.com’,’测试邮件标题’,’这里是邮件内容,从以上三种方法的优缺点比较来看,我们当然采取第三种方法,不需要在服务器上装别的组件和程序。我们可以在SQL代理中建立一个作业,调度设为每天下午6点,执行的数据库备份语句和发送邮件的SQL如下:

  EXEC @hr = sp_OASetProperty @MailID, ‘Body’, @Body

2.调用存储过程

代码如下复制代码

  EXEC @hr = sp_OASetProperty @MailID, ‘BCC’,@BCC

对于数据变更,可再Trigger中调用这一存储过程。也可以在SQL Server
Agent中建立作业来调用,只需要传入相应参数即可。
exec sp_send_mail
‘发件者Email(采用存储过程中写入的那个Email)’,’收件者列表,各Email用分号隔开’,’主题’,’内容’

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

  EXEC @hr = sp_OASetProperty @MailID, ‘CC’, @CC

注:以上存储过程有个缺陷,即@Body定义为varchar类型,而SQL
Server中varchar类型长度最大是8000。也就是说一次最多只能发送8000个字符的内容。

–下面获取备份之后文件的大小declare @size intdeclare @sizeM decimal (5,
2)

  EXEC @hr = sp_OASetProperty @MailID, ‘Subject’, @Subject

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

  EXEC @hr = sp_OASetProperty @MailID, ‘To’, @To

  EXEC @hr = sp_OAMethod @MailID, ‘Send’, NULL

  EXEC @hr = sp_OADestroy @MailID

  调用方法:

  exec sp_send_cdontsmail
‘[email protected]’,'[email protected]’,’测试邮件标题’,’这里是邮件内容,推荐一个好的小说站,好看书吧,’

  三、使用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 =
‘[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(“‘

  EXEC @hr = sp_OASetProperty @object,
‘Configuration.fields(“‘,
@smtpserver

  –下面三条语句是smtp验证,如果服务器需要验证,则必须要这三句,你需要修改用户名和密码

  EXEC @hr = sp_OASetProperty @object,
‘Configuration.fields(“‘

  EXEC @hr = sp_OASetProperty @object,
‘Configuration.fields(“

  EXEC @hr = sp_OASetProperty @object,
‘Configuration.fields(“

  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
‘[email protected]’,'[email protected]’,’测试邮件标题’,’这里是邮件内容,手机主题,’

  从以上三种方法的优缺点比较来看,我们当然采取第三种方法,不需要在服务器上装别的组件和程序。我们可以在SQL代理中建立一个作业,调度设为每天下午6点,执行的数据库备份语句和发送邮件的SQL如下:

  declare @dbname varchar(50)

  set @dbname = ‘dbtest’ –设置数据库名

  declare @filename nvarchar(100)

  declare @time datetime

  set @time = getdate()

  set @filename=
‘D:数据库自动备份'[email protected]+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=’数据库自动备份成功。

  数据库名:'[email protected]+’

  备份文件名:'[email protected]+’

  备份文件大小:’+convert(varchar,@sizeM)+’M

  备份时间:’+CONVERT(varchar, @time, 120 )+’

  这是一封系统自动发出的邮件,用来每天报告数据库自动备份情况,请不要直接回复。’

  –print @content

  –发送邮件

  EXECUTE dbtest.dbo.sys_sendmail
‘[email protected]’,'[email protected]’,

  ’数据库自动备份日报’,@content

  go