SQL 维护计划在未配置邮件时发送邮件

使用SQL维护计划可以帮助我们自动化数据库的维护任务，比如备份、索引重组和统计信息更新等。然而，在某些情况下，我们可能会遇到一个问题，即在未配置邮件通知时如何及时获得维护计划的执行结果。本文将介绍如何 ，并提供相应的案例代码来解决这个问题。

sql
CREATE PROCEDURE sp_send_email
    @to_email VARCHAR(100),
    @subject VARCHAR(100),
    @body VARCHAR(MAX)
AS
BEGIN
    DECLARE @mailitem_id INT
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'YourMailProfile',
        @recipients = @to_email,
        @subject = @subject,
        @body = @body,
        @body_format = 'HTML',
        @mailitem_id = @mailitem_id OUTPUT
    SELECT @mailitem_id AS 'MailItemID'
END

sql
-- 创建维护计划
EXEC msdb.dbo.sp_add_maintenance_plan
    @plan_name = 'YourMaintenancePlan',
    @schedule_name = 'YourSchedule',
    @subplan_name = 'YourSubplan',
    @plan_id = @plan_id OUTPUT
-- 将维护任务添加到维护计划中
EXEC msdb.dbo.sp_add_maintenance_plan_subplan
    @plan_id = @plan_id,
    @subplan_name = 'YourSubplan',
    @job_name = 'YourJob',
    @command = 'EXEC sp_send_email ''your_email@example.com'', ''Maintenance Plan Completed'', ''The maintenance plan has completed successfully.'''
-- 启动维护计划
EXEC msdb.dbo.sp_start_job 'YourJob'

sql
CREATE PROCEDURE sp_send_email
    @to_email VARCHAR(100),
    @subject VARCHAR(100),
    @body VARCHAR(MAX)
AS
BEGIN
    DECLARE @mailitem_id INT
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'YourMailProfile',
        @recipients = @to_email,
        @subject = @subject,
        @body = @body,
        @body_format = 'HTML',
        @mailitem_id = @mailitem_id OUTPUT
    SELECT @mailitem_id AS 'MailItemID'
END

sql
-- 创建维护计划
EXEC msdb.dbo.sp_add_maintenance_plan
    @plan_name = 'Sales_Backup',
    @schedule_name = 'DailyBackup',
    @subplan_name = 'BackupSubplan',
    @plan_id = @plan_id OUTPUT
-- 将备份任务添加到维护计划中
EXEC msdb.dbo.sp_add_maintenance_plan_subplan
    @plan_id = @plan_id,
    @subplan_name = 'BackupSubplan',
    @job_name = 'BackupJob',
    @command = 'BACKUP DATABASE Sales TO DISK = ''C:\Backup\sales.bak''; EXEC sp_send_email ''your_email@example.com'', ''Backup Completed'', ''The backup has completed successfully.'''
-- 启动维护计划
EXEC msdb.dbo.sp_start_job 'BackupJob'