首页 > 数据库 > SQL Server >

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

2011-06-09

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

最近在一个做企业的一个内部系统数据库采用的是SQL2000,为了保证数据的安全性,需要每天下班之后做数据备份,并且通过邮件的方式通知管理员备份情况。备份数据库很简单,用SQL代理建立一个作业,每天定时备份数据库即可,通过SQL2000来发邮件的话,在网上找了些资料,发现有多种方式可以采用。
一、通过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,测试邮件标题,这里是邮件内容,推荐一个好的小说站,好看书吧,http://www.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,测试邮件标题,这里是邮件内容,手机主题,http://www.shouji138.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 = NBIS_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

相关文章
最新文章
热点推荐