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

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 floatState of Qatar/1024/1024–print @sizeM–邮件内容declare @content
varchar(二〇〇一卡塔尔(قطر‎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

  order by backup_start_date desc

对此数据变动,可再Trigger中调用这一囤积进度。也得以在SQL Server
Agent中创设作业来调用,只须要传入相应参数就能够。
exec sp_send_mail
‘发件者Email(采纳储存进程中写入的那三个Email卡塔尔’,’收件者列表,各Email用分号隔断’,’主旨’,’内容’

测验邮件题目’,’这里是邮件内容,推三、使用CDOSYS 微软曾在 Windows
2004、Windows XP 以至 Windows 二〇〇一 中淘汰了
CDONTS,所以使用CDOSYS是时下最佳的缓和方案。使用CDOSYS能够利用远程的SMTP服务器来发送邮件,大家由此测验163腾讯网的无偿邮箱,能够平常发送邮件,相应的存放进程如下:

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

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

  EXEC @hr = sp_OADestroy @MailID

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

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

  Declare @smtpserver varchar(50卡塔尔 –SMTP服务器地址

— 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

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

  select top 1 @size=backup_size

注:以上存款和储蓄进程有个破绽,即@Body定义为varchar类型,而SQL
Server中varchar类型长度最大是8000。也正是说三次最七只可以发送8000个字符的故事情节。

–上边获取备份之后文件的高低declare @size intdeclare @sizeM decimal (5,
2卡塔尔国

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

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

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

  –print @content

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

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

  调用存款和储蓄进程发送邮件:exec sys_sendmail
‘[email protected]’,'[email protected]’,’测量试验邮件题目’,’这里是邮件内容,手提式有线电话机宗旨,’



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
(“‘

代码如下复制代码

  go

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
*******************************************/

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 卡塔尔(قطر‎,’-‘,”卡塔尔,’ ‘,”卡塔尔国,’:’,”State of Qatar,1,14 卡塔尔+’.bak’–print
@filenameBACKUP DATABASE dbtest TO DISK = @filename WITH NOINIT,
NOUNLOAD, NAME = N’BIS_data_backup’, NOSKIP , STATS = 10, NOFORMAT

  declare @sizeM decimal (5, 2)

— 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

AS

  @From varchar(100),

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

调用存款和储蓄进度发送邮件:exec sys_sendmail
‘someone@shouji138.com’,’someone2@hks8.com’,’测量检验邮件标题’,’这里是邮件内容,从上述两种方式的优短处比较来看,大家当然接纳第三种方法,没有必要在服务器上装其他零零器件和顺序。大家可以在SQL代理中树立三个学业,调治设为每一天中午6点,实施的数据库备份语句和出殡和安葬邮件的SQL如下:

  EXEC @hr = sp_OADestroy @object

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

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

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

— 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

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

本小说介绍了有关sqlserver自动备份数据库且邮箱发送邮箱状态,有需求让机器自动备份数据库的爱侣能够看看本小说的做法。

  END

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

调用方法:

  EXEC @hr = sp_OASetProperty @object, ‘TextBody’, @Body

对此DB中的资料转移,不常会有寄Mail文告相关人士的供给。下边是落到实处这一效果的一种情势

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

  set @smtpserver = ‘smtp.163.com’

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

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

  BACKUP DATABASE dbtest TO DISK = @filename WITH NOINIT, NOUNLOAD,
NAME = N’BIS_data_backup’, NOSKip , STATS = 10, NOFORMAT

SQL Server发送邮件的囤积进度

一、通过SQL Mail SQL Mail 提供了一种从 Microsoft SQL Server
发送和读书电子邮件的回顾方法。原理是透过调用服务器上面包车型大巴 MAPI
子系统来拓宽邮件发送,所以服务器下边需求设置诸如 Microsoft Outlook
之类的 MAPI
顾客端,何况在发送邮件的时候,Outlook必得处于张开的情景。具体的设置方法能够因而网络查询。二、使用CDONTS
通过调用本机的SMTP服务来发送邮件,所以服务器上必得设置IIS和SMTP。相应的仓库储存进程为

  二、使用CDONTS

— Sample error handling.

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_OASetProperty @object,
‘Configuration.fields(“‘

2.调用存款和储蓄进度

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

  set @sizeM = CAST(@size as float)/1024/1024

代码如下复制代码

  Declare @smtpusername varchar(50卡塔尔 –SMTP服务器客户名

  @Body varchar(4000) =” “

  EXEC @hr = sp_OASetProperty @object, ‘Bcc’, @Bcc

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

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

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

  @Subject varchar(100),

  –print @filename

  AS

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

   前段时间在一个做集团的壹在那之中间系统,数据库选取的是SQL贰零零壹,为了保险数据的安全性,需求每一天下班未来做数据备份,而且经过邮件的艺术通报管理员备份意况。备份数据库很简短,用SQL代理创立一个作业,天天按期备份数据库就能够,通过SQL2003来发邮件的话,在网络找了些资料,发现存二种办法得以行使。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

CopyRight © 2015-2020 金沙中心城 All Rights Reserved.
网站地图xml地图