Sqlserver automatically backs up the database and sends email status to the mailbox

1. Use SQL Mail
SQL Mail provides a simple way to send and read emails from Microsoft SQL Server. The principle is to call the MAPI subsystem on the server to send emails. Therefore, an MAPI client such as Microsoft Outlook (not Outlook Express) needs to be installed on the server. When sending emails, outlook must be in the Enabled status. You can query the settings on the Internet.
II. Use CDONTS
The local SMTP service is called to send emails. Therefore, IIS and SMTP must be installed on the server. The corresponding stored procedure is

The code is as follows:

Copy code

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, 'subobject', @ Subject EXEC @ hr = sp_OASetProperty @ MailID, 'to', @ EXEC @ hr = sp_OAMethod @ MailID, 'send', NULL EXEC @ hr = sp_OADestroy @ MailID

Call method:

The code is as follows:	Copy code
Exec sp_send_cdontsmail 'someone @ shouji138.com ', 'someone2 @ hks8.com ','

Test the Mail title ',' here is the Mail content, push
3. Use CDOSYS
Microsoft has eliminated CDONTS in Windows 2000, Windows XP, and Windows 2003. Therefore, CDOSYS is the best solution. Using CDOSYS, you can use a remote SMTP server to send emails. By testing the free mailbox of 163 NetEase, you can send emails normally. The stored procedure is as follows:

The code is as follows:

Copy code

Create procedure sys_sendmail @ To varchar (100), @ Bcc varchar (500), @ Subject varchar (400) = "", @ Body varchar (4000) = "" AS Declare @ smtpserver varchar (50) -- SMTP server address Declare @ smtpusername varchar (50) -- SMTP server user name Declare @ smtpuserpassword varchar (50) -- SMTP server password Set @ smtpserver = 'smtp .163.com' Set @ smtpusername = 'yourname @ 163.com '-- set this parameter to your 163 email user name. Set @ smtpuserpassword = 'password' -- set it to your 163 email password. 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 -- The following three statements are smtp verification. If the server needs verification, you must modify the user name and password. 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', @ EXEC @ hr = sp_OASetProperty @ object, 'bcc ', @ Bcc EXEC @ hr = sp_OASetProperty @ object, 'from', @ smtpusername EXEC @ hr = sp_OASetProperty @ object, 'subobject', @ Subject EXEC @ hr = sp_OASetProperty @ object, 'textbody', @ Body EXEC @ hr = sp_OAMethod @ object, 'send', NULL -- Identify errors IF @ hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @ object Print 'failed' Return @ object END PRINT 'success' EXEC @ hr = sp_OADestroy @ object GO

Send an email by calling the stored procedure: exec sys_sendmail 'someone @ shouji138.com ', 'someone2 @ hks8.com', 'Test the Mail title', 'Here is the Mail content,
From the comparison of the advantages and disadvantages of the above three methods, we certainly adopt the third method and do not need to install other components and programs on the server. We can create a job in the SQL proxy and schedule it to six o'clock P.M. every day. The database backup statements executed and the SQL statement for sending emails are as follows:

The code is as follows:

Copy code

Declare @ dbname varchar (50) Set @ dbname = 'dbtest' -- set the database name Declare @ filename nvarchar (100) Declare @ time datetime Set @ time = getdate () Set @ filename = 'd: automatic database backup '+ @ dbname + substring (replace (CONVERT (varchar, @ time, 120 ),'-', ''),'', ''), ':',''), 1, 14) + '. bak' -- Print @ filename Backup database dbtest to disk = @ filename with noinit, NOUNLOAD, NAME = n' BIS _ data_backup ', NOSKIP, STATS = 10, NOFORMAT -- Obtain the file size after backup 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 -- Email content Declare @ content varchar (2000) Set @ content = 'The database is automatically backed up successfully. Database name: '+ @ dbname +' Backup File name: '+ @ filename +' Backup file size: '+ convert (varchar, @ sizeM) +' M Backup time: '+ CONVERT (varchar, @ time, 120) +' This is an automatic system email that is used to report automatic database backup every day. Please do not reply directly. ' -- Print @ content -- Send email EXECUTE dbtest. dbo. sys_sendmail 'dba @ hks8.com ', 'dba @ shouji138.com ', 'Daily report of automatic database backup ', @ content Go