SQL Server R2 regularly backs up the database and sends mail notifications

Configure the database's mail settings before you can send messages.

2.

3.

4.

5.

6.

7.

8.

9.

10.

The total preview map,

Execute this paragraph (send backup mail first, then make data backup, insert yesterday's send data into another table, so that the data in the table is also very small, the query also quickly, make full use of the database tablespace, the table is a way to improve the query and insert a very fast solution)

/** * * * EMAIL Notification * * * **/ UseOpenmasGODECLARE @mailContent NVARCHAR( -),@num int,@totalMessageCount int,@sys_usr NVARCHAR( -);SET @sys_usr = System_user;SELECT @num = ISNULL(COUNT(1),0) fromSmssentlogWHERE  Year(Sendtime)=  Year(GETDATE()-1) and MONTH(Sendtime)= MONTH(GETDATE()-1) and  Day(Sendtime)=  Day(GETDATE()-1)SELECT @totalMessageCount = ISNULL(SUM(Messagecount),0) fromSmssentlogWHERE  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 = 'Backup send record table message content: Time:'+ CONVERT(varchar( -),GETDATE(), -)+', number of backup strips:'+CAST(@num  as NVARCHAR)+'bar, Total send volume:'+CAST(@totalMessageCount  as NVARCHAR)+', log in to the database account:'+@sys_usr+'from Openmas Machine' --PRINT @mailContent UsemsdbEXECDbo.sp_send_dbmail@recipients='[email protected]',@subject='database table data Backup notification',@body =@mailContentGO/*early morning (two o ' clock) insert text messages from yesterday (2015-1-9) into the table for the specified month (SMSSentLog201501)*/ UseOpenmas--Defining Parameters--yearDECLARE @year NVARCHAR(Ten)--MonthDECLARE @month NVARCHAR(Ten)--DayDECLARE @day NVARCHAR(Ten)--Dynamic Table nameDECLARE @tableName NVARCHAR( -)--Dynamic Build ScriptsDECLARE @Sql NVARCHAR( -)--Assignment (what was the day of yesterday's month)SET @year =  Year(GETDATE()-1)SET @month =MONTH(GETDATE()-1)SET @day  =  Day(GETDATE()-1)         --Dynamic Build table name (name to import to backup table)    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+';'    --splicing DELETE statement, whether to delete    SET @Sql  = @Sql + 'DELETE from Smssentlog WHERE year (sendtime) ='+@year+'and MONTH (sendtime) ='+@month+'And day (sendtime) ='+@day+';'    --PRINT @Sql    --Execute SQL    EXECsp_executesql@sql

SQL Server R2 regularly backs up the database and sends mail notifications