Automatically backs up databases and sends mail reporting databases

Recently in an enterprise to do an internal system, the database is SQL2000, in order to ensure the security of data, need to do daily after work to do data backup, and mail to notify the administrator of the backup situation. Backup database is very simple, with the SQL Agent to build a job, a regular daily backup database, through the SQL2000 to send e-mail words, on the Internet to find some information, found that there are many ways to use.

One, through SQL Mail

SQL Mail provides a simple way to send and read e-mail messages from Microsoft SQL Server. The principle is to send mail by calling the MAPI subsystem on the server, so the server needs to install MAPI clients such as Microsoft Outlook (which cannot be Outlook ExPRess), and when sending messages, Outlook must be in an open state. The specific setting method can be queried through the Internet.

Ii. Use of CDONTS

By invoking the native SMTP service to send mail, IIS and SMTP must be installed on the server. The corresponding stored procedure is

CREATE PROCEDURE [dbo]. [Sp_send_cdontsmail]

@From varchar (100),

@To varchar (100),

@Subject varchar (100),

@Body varchar (4000),

@CC varchar = NULL,

@BCC varchar = 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, ' Subject ', @Subject

EXEC @hr = sp_OASetProperty @MailID, ' to ', @To

EXEC @hr = sp_OAMethod @MailID, ' Send ', NULL

EXEC @hr = sp_OADestroy @MailID

Call Method:

exec sp_send_cdontsmail ' someone@shouji138.com ', ' someone2@hks8.com ', ' test message title ', ' Here is the mail content, recommend a good novel station, good-looking book, http://www.hks8.com '

Iii. Use of CDOSYS

Microsoft has eliminated CDONTS in Windows 2000, Windows XP, and Windows 2003, so using CDOSYS is the best solution at the moment. Use CDOSYS can use the remote SMTP server to send mail, we test 163 NetEase's free mailbox, can send the mail, the corresponding stored procedures are as follows:

CREATE PROCEDURE sys_sendmail @To varchar (m), @Bcc varchar (+), @Subject varchar (400) = "",

@Body varchar (4000) = ""

As

Declare @smtpserver varchar--SMTP server address

Declare @smtpusername varchar--SMTP Server user name

Declare @smtpuserpassWord varchar--SMTP server password

Set @smtpserver = ' smtp.163.com '

Set @smtpusername = ' yourname@163.com '--set to your 163 mailbox username

Set @smtpuserpassword = ' Password '--this is set to your 163-mailbox 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 authentication and if the server needs to be validated, you need to change the username 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 ', @To

EXEC @hr = sp_OASetProperty @object, ' Bcc ', @Bcc

EXEC @hr = sp_OASetProperty @object, ' from ', @smtpusername

EXEC @hr = sp_OASetProperty @object, ' Subject ', @Subject

EXEC @hr = sp_OASetProperty @object, ' TextBody ', @Body

EXEC @hr = sp_OAMethod @object, ' Send ', NULL

--Error judgment

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @object

print ' failed '

Return @object

End

PRINT ' Success '

EXEC @hr = sp_OADestroy @object

Go

Invoke stored procedures to send mail: exec sys_sendmail ' someone@shouji138.com ', ' someone2@hks8.com ', ' test message title ', ' here is the message content, the cell phone theme, http://www.shouji138.com '

From the advantages and disadvantages of the above three methods, we certainly take a third approach, do not need to install other components and programs on the server. We can set up a job in the SQL Agent, scheduled to 6 o'clock in the afternoon every day, the database backup statements executed and the SQL to send the message are as follows:

DECLARE @dbname varchar (50)

Set @dbname = ' dbtest '--Set database name

declare @filename nvarchar (100)

DECLARE @time datetime

Set @time = GETDATE ()

Set @filename = ' D: Database automatic backup ' + @dbname +substring (replace (replace (varchar, @time, 120), '-', ', ', ', ', '), ' : ', ', 1,14) + '. Bak '

--print @filename

BACKUP DATABASE dbtest to DISK = @filename with Noinit, nounload, NAME = N ' Bis_data_backup ', noskip, STATS = ten, Noformat

--Below gets the size of the file after the 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

--Mail content

DECLARE @content varchar (2000)

Set @content = ' Database automatic backup succeeded.

Database name: ' + @dbname + '

Backup filename: ' + @filename + '

Backup file Size: ' +convert (varchar, @sizeM) + ' M

Backup time: ' +convert (varchar, @time, 120) + '

This is a system automatically sent messages, used to report the automatic backup of the database every day, please do not reply directly. '

--print @content

--Send mail

EXECUTE dbtest.dbo.sys_sendmail ' dba@hks8.com ', ' dba@shouji138.com ',

' Database automatic backup daily ', @content

Go