你的位置:首页 > 数据库

[数据库]SqlServer 2008 R2定时备份数据库,并且发送邮件通知


先配置数据库的邮件设置,这样才可以发送邮件。

2.

3.

4.

5.

6.

7.

8.

9.

10.

总的预览图,如图

 

执行这一段(先发送备份邮件,然后进行数据备份,将昨天的发送数据插入到另一张表中,这样表中的数据也很小,查询也很快,充分利用数据库的表空间,进行拆表是一件提高查询和插入速度很快的一种方案)

/******发送邮件通知******/USE OpenMasGODECLARE @mailContent NVARCHAR(2000),@num int,@totalMessageCount int,@sys_usr NVARCHAR(30);SET @sys_usr = SYSTEM_USER;SELECT @num = ISNULL(COUNT(1),0) FROM SMSSentLog WHERE YEAR(SendTime) = YEAR(GETDATE()-1) AND MONTH(SendTime)= MONTH(GETDATE()-1) AND DAY(SENDTIME) = DAY(GETDATE()-1)SELECT @totalMessageCount = ISNULL(SUM(MessageCount),0) FROM SMSSentLog WHERE YEAR(SendTime) = YEAR(GETDATE()-1) AND MONTH(SendTime)= MONTH(GETDATE()-1) AND DAY(SENDTIME) = DAY(GETDATE()-1)--PRINT cast(@num as nvarchar) + '-'+ cast( @totalMessageCount as nvarchar)SET @mailContent = '备份发送记录表邮件内容:时间:'+ CONVERT(varchar(100), GETDATE(), 25)+',备份条数:'+CAST(@num AS NVARCHAR) +'条,总发送量:'+CAST( @totalMessageCount AS NVARCHAR)+',登录数据库账号:'+@sys_usr+' 来自OpenMas机' --PRINT @mailContent USE msdbEXEC dbo.sp_send_dbmail@recipients='yanghenglian@163.com',@subject='数据库表数据备份通知',@body =@mailContentGO/*每天凌晨(两点钟)将昨天(2015-1-9)的发送短信插入到指定月份的表(SMSSentLog201501)*/USE OpenMas--定义参数--年份DECLARE @year NVARCHAR(10)--月份DECLARE @month NVARCHAR(10)--日DECLARE @day NVARCHAR(10)--动态表名称DECLARE @tableName NVARCHAR(20)--动态构建脚本DECLARE @Sql NVARCHAR(2000)--赋值(昨天的年月日是多少)SET @year = YEAR(GETDATE()-1)SET @month =MONTH(GETDATE()-1)SET @day = DAY(GETDATE()-1)     --动态构建表名称(需要导入到备份表的名称)  SET @tableName = 'SMSSentLog'+ @year + CASE WHEN LEN(@month)=1 THEN '0'+ @month ELSE @month END  --PRINT @year + '-' + @month + '-' + @tableName  SET @Sql = N'INSERT INTO '+ @tableName +'(Id,Channel,SystemCode,CompanyId,CompanyCode,CompanyName,UserId,UserCode,UserName,AccountCode,ApplicationCode,MessageID,MessageContent,MessageCode,ExtendCode,DestinationAddress,CustomerName,SendType,SendTime,IsWapPush,MessageCount,WapUrl,CreateTime,GatawayStatus) SELECT Id,Channel,SystemCode,CompanyId,CompanyCode,CompanyName,UserId,UserCode,UserName,AccountCode,ApplicationCode,MessageID,MessageContent,MessageCode,ExtendCode,DestinationAddress,CustomerName,SendType,SendTime,IsWapPush,MessageCount,WapUrl,CreateTime,GatawayStatus FROM SMSSentLog WHERE YEAR(SendTime) = '+@year+' AND MONTH(SendTime)='+@month+' AND DAY(SENDTIME) = '+@day+';'  --拼接删除语句,是否要删除  SET @Sql = @Sql + 'DELETE FROM SMSSentLog WHERE YEAR(SendTime) ='+@year+' AND MONTH(SendTime)='+@month+' AND DAY(SENDTIME) ='+@day+';'  --PRINT @Sql  --执行Sql  EXEC sp_executesql @sql