Nice technique, Lori!

I parameterized a few things because, through no fault of my own, my server configurations are like a box of chocolates.

I don't have to deal with clustered SQL Server instances, so this code is for STAND-ALONE INSTANCES ONLY.

Replace 'fake@fake.com' with your individual address, 'smooth' with an existing operator, and 'high' with an existing DBMail profile.

Run the code, run the restart_ALERT email job, marvel at the email, change the email to your DBA group's email address, run the code twice for some reason, and you are done.

No warranties expressed or implied.

DECLARE@jobId binary(16)

, @servername varchar(50) = (select @@Servername)

, @operator varchar(50) = 'smooth'

, @profile varchar(50) = 'high'

, @email varchar(50) = 'fake@fake.com'

DECLARE@commandstage varchar(MAX) = N'SET NOCOUNT ON

DECLARE @body1 NVARCHAR(MAX)

-- instructions

SET @body1=''

The ' + @servername + ' Instance has been restarted.''

-- table attributes

SET @body1=@body1+''<table border="2" cellspacing="2" cellpadding="2">''

-- column headers

SET @body1=@body1+ ''<tbody align="left" style="font-family:Arial; font-size: 11;" <TR><TH>Service Name</TH><TH>Startup Type</TH><TH>Status</TH><TH>Startup Time</TH></TR></tbody>''

-- data

SELECT @body1=@body1 +''<tbody align="left" style="font-family:Arial; font-size: 11;" <TR><TD>''+servicename+''</TD><TD>''+startup_type_desc+''</TD><TD>''+status_desc+''</TD><TD>''+CAST(last_startup_time AS VARCHAR(30))+''</TD></TR></tbody>'' FROM sys.dm_server_services

-- Send an html formatted e-mail to notify of restart

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ''' + @profile + ''',

@recipients = ''' + @email + ''',

@subject = ''SQL restart on ' + @servername + ''',

@body = @body1,

@body_format = ''HTML''

SET NOCOUNT OFF'

--out with the old

SELECT @jobId = job_id

FROM msdb.dbo.sysjobs WHERE (name = N'restart_ALERT')

IF (@jobId IS NOT NULL)

BEGIN

EXEC msdb.dbo.sp_delete_job @jobId

END

--in with the new

EXEC msdb.dbo.sp_add_job@job_name=N'restart_ALERT',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=2,

@notify_level_netsend=2,

@notify_level_page=2,

@delete_level=0,

@description=N'sends email to DBAGroup after SQL Agent starts',

@category_name=N'Database Maintenance',

@owner_login_name=N'sa',

@notify_email_operator_name= @operator,

@job_id = @jobId OUTPUT

select @jobId

EXEC msdb.dbo.sp_add_jobserver@job_name=N'restart_ALERT'

, @server_name = @servername

EXEC msdb.dbo.sp_add_jobstep@job_name=N'restart_ALERT'

, @step_name=N'send email'

, @step_id=1

, @cmdexec_success_code=0

, @on_success_action=1

, @on_fail_action=2

, @retry_attempts=0

, @retry_interval=0

, @os_run_priority=0

, @subsystem=N'TSQL'

, @command= @commandstage

, @database_name=N'master'

, @flags=0

EXEC msdb.dbo.sp_update_job @job_name=N'restart_ALERT',

@enabled=1,

@start_step_id=1,

@notify_level_eventlog=0,

@notify_level_email=2,

@notify_level_netsend=2,

@notify_level_page=2,

@delete_level=0,

@description=N'sends email to DBAGroup after SQL Agent starts',

@category_name=N'Database Maintenance',

@owner_login_name=N'sa',

@notify_email_operator_name= @operator,

@notify_netsend_operator_name=N'',

@notify_page_operator_name=N''

DECLARE @schedule_id int

EXEC msdb.dbo.sp_add_jobschedule@job_name=N'restart_ALERT',

@name=N'on SQL Agent start',

@enabled=1,

@freq_type=64,

@freq_interval=1,

@freq_subday_type=0,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=1,

@active_start_date=20151222,

@active_end_date=99991231,

@active_start_time=0,

@active_end_time=235959, @schedule_id = @schedule_id OUTPUT

select @schedule_id

GO