SQL Server Restart Notification

If you ever wanted to be notified by email when your SQL Servers are restarted, here are a couple of scripts which will do just that. It relies on using the "run on Agent Manager startup" option in SQL Jobs.

An alternative method uses sp_procoption

SQL Server Restart Notification Scripts (Size 4 KB)

For SQL Server 2005

What it does:

This script, which is intended for SQL Server 2005 instances, sets up a scheduled job which will run when the Agent Manager is started. Should tempdb be recently created then this usually indicates that the SQL Server instance has been started. The script then issues an email with the date and time the server was started.

Requirements:

SQL Server 2005 and above.
You must already have Database Mail setup to support sp_send_dbmail.

Installation:

Run the script.
Go to the newly created job called "SQL Server Restart Notification" under SQL Server Agent + Jobs.
Edit the job and go to Step 1.
Change the line @recipients = 'someone@domainname.com' to be the email address you want to send the alerts to.
Optional: You may also enable the Notifications section so that someone is alerted if this particular job fails for whatever reason.

	
-- SQL Server Restart Notification 2005.sql
-- (C) Copyright 2009-2010 John Buoro, VirtualObjectives
-- http://www.VirtualObjectives.com.au

-- This script is intended for SQL Server 2005.
-- This script sets up a scheduled job which will run when the Agent Manager is started.
-- Should tempdb be recently created then this usually indicates that the SQL Server instance has been started.
-- The script then issues an email with the date and time the server was started.

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'SQL Server Restart Notification', 
        @enabled=1, 
        @notify_level_eventlog=2, 
        @notify_level_email=2, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'SQL Server Restart Notification', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', 
        @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SQL Server Restart Notification', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=1, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'-- SQL Server Restart Notification 2005.sql
-- (C) Copyright 2009-2010 John Buoro, VirtualObjectives
-- http://www.VirtualObjectives.com.au

DECLARE @UpTimeDays int
DECLARE @SQLServerStarted varchar(20)
DECLARE @rc int
DECLARE @msg varchar(1000)

SET @UpTimeDays = (select DateDiff(D, CrDate, GetDate()) from master..SysDatabases where name = ''TempDb'')
IF @UpTimeDays = 0
BEGIN
    SET @SQLServerStarted = (select convert(varchar(20), CrDate, 113) from master..SysDatabases where name = ''TempDb'')
    SET @msg = ''The SQL Server <b>'' + @@SERVERNAME + ''</b> was restarted on <b>'' + @SQLServerStarted + ''</b>''
    EXEC @rc = msdb.dbo.sp_send_dbmail
        @recipients = ''someone@domainname.com'',
        @importance = ''high'',
        @subject = ''SQL Server Restart Notification'',
        @body_format = ''html'',
        @body = @msg,
        @exclude_query_output = 1
    IF @rc = 1 RAISERROR(''sp_send_dbmail Failed'', 16, 1)
END
', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule', 
        @enabled=1, 
        @freq_type=64, 
        @freq_interval=0, 
        @freq_subday_type=0, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20090402, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

For SQL Server 2000

What it does:

This script, which is intended for SQL Server 2000 instances, sets up a scheduled job which will run when the Agent Manager is started. Should tempdb be recently created then this usually indicates that the SQL Server instance has been started. The script then issues an email with the date and time the server was started.

Requirements:

SQL Server 2000 only.
You must have the ability to send email using either the native xp_sendmail OR a third-party utility like xp_smtp_sendmail.
This script uses xp_smtp_sendmail, so if you use something else, then you will need to change the script accordingly.

Installation:

Run the script.
Go to the newly created job called "SQL Server Restart Notification" under SQL Server Agent + Jobs.
Edit the job and go to Step 1.
Change "@domainname.com" in @svr to reflect your mail domain.
Change the line @TO = ''someone@domainname.com'' to be the email address you want to send the alerts to.
In the line @server = ''SMTPSERVER'', change the name to your SMTP server.
Optional: You may also enable the Notifications section so that someone is alerted if this particular job fails for whatever reason.


-- SQL Server Restart Notification 2000.sql
-- (C) Copyright 2009 John Buoro, VirtualObjectives
-- http://www.VirtualObjectives.com.au

-- This script is intended for SQL Server 2000 only.
-- This script sets up a scheduled job which will run when the Agent Manager is started.
-- Should tempdb be recently created then this usually indicates that the SQL Server instance
-- has been started.
-- The script then issues an email with the date and time the server was started.

BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'SQL Server Restart Notification')       
  IF (@JobID IS NOT NULL)    
  BEGIN  
  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''SQL Server Restart Notification'' since there is already a 
	multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'SQL Server Restart Notification' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , 
	@job_name = N'SQL Server Restart Notification', @owner_login_name = N'sa', 
	@description = N'SQL Server Restart Notification', @category_name = N'[Uncategorized (Local)]', 
	@enabled = 1, @notify_level_email = 2, @notify_level_page = 0, @notify_level_netsend = 0, 
	@notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, 
	@step_name = N'SQL Server Restart Notification', 
	@command = N'-- SQL Server Restart Notification 2000.sql
-- (C) Copyright 2009 John Buoro, VirtualObjectives
-- http://www.VirtualObjectives.com.au

DECLARE @UpTimeDays int
DECLARE @SQLServerStarted varchar(20)
DECLARE @svr varchar(100)
DECLARE @rc int
DECLARE @msg varchar(1000)

SET @UpTimeDays = (select DateDiff(D, CrDate, GetDate()) from master..SysDatabases where name = ''TempDb'')
IF @UpTimeDays = 0
BEGIN
    SET @SQLServerStarted = (select convert(varchar(20), CrDate, 113) from master..SysDatabases where name = ''TempDb'')
    SET @msg = ''The SQL Server <b>'' + @@SERVERNAME + ''</b> was restarted on <b>'' + @SQLServerStarted + ''</b>''
    SET @svr = (select @@SERVERNAME) + ''@domainname.com''
    -- xp_smtp_sendmail http://www.sqldev.net/xp/xpsmtp.htm
    EXEC @rc = master.dbo.xp_smtp_sendmail
        @FROM = @svr,
        @FROM_NAME = @@SERVERNAME,
        @TO = ''someone@domainname.com'',
        @priority = ''HIGH'',
        @subject = ''SQL Server Restart Notification'',
        @type = ''text/html'',
        @message = @msg,
        @server = ''SMTPSERVER''
    IF @rc = 1 RAISERROR(''xp_smtp_sendmail Failed'', 16, 1)
END
', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', 
	@cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, 
	@output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, 
	@on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job schedules
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Schedule', 
	@enabled = 1, @freq_type = 64
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: