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

/******发送邮件通知******/
USE OpenMas
GO
DECLARE @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 msdb
EXEC dbo.sp_send_dbmail
@recipients='yanghenglian@163.com',
@subject='数据库表数据备份通知',
@body =@mailContent
GO

/*每天凌晨（两点钟）将昨天（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

复制