SQL Server Notifying Successful of Successful Restores

I can't count the number of times I get request for a Database to get restored, and the customer needs to know when it's done right away. With databases over a TB, and varying hardware nailing down completion time is almost impossible. So that I don't spend my nights or weekends waiting for a restore to complete I've come up with a little framework to alert the customer of completion. Essentially the script below checks on the status of the DB I'm restoring, once 0 or Restored it sends out a completion email. The reason I raise an error if not restored is because I place this into a job with a schedule of run once. I then setup 100 retries to occur with a retry occuring every 5 minutes in the advanced tab of the Job definition. Essentially if the db is restored it sends an email out to the customer and exist the job. If not it registers an error to SQL Agent which proceeds to wait 5 minutes before trying again.

 

 

Declare @DBName varchar(100)

Declare @State smallint

Set @DBName =
'DBName'

select @State =
state
from
sys.databases

where Name = @dbname

 

If @State = 0

Begin

EXEC msdb.dbo.sp_send_dbmail

            @profile_name =
'Admin',

            @recipients =
'customer@zillow.com',

         @subject =
'DBRestoreComplete'

End

Else

Begin

Raiserror ('DB Not Restored', 16, 1)

end