Automatically back up databases and send email reports to databases

Recently, in an internal system of an enterprise, the database uses SQL2000. To ensure data security, you need to back up data after work every day and notify the administrator about the backup by email. It is very easy to back up the database. You can create a job using an SQL proxy and regularly back up the database every day. If you send an email via SQL2000, you can find some information online and find that there are multiple methods to use.

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

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:

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

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:

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

Call the stored procedure to send mail: exec sys_sendmail 'someone @ shouji138.com ', 'someone2 @ hks8.com', 'test mail title', 'Here is the mail content, mobile phone theme, http://www.shouji138.com'

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:

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