SQL Server Performance

Have SQL email you if your Litespeed (or native) backups fail

Discussion in 'Michael Berry' started by MichaelB, Feb 16, 2010.

  1. MichaelB Member

    If you run Quest Litespeed backups I have the blog post for you!I have a job that I created that uses Database Mail and my Litespeed Central database. It spits out a email in HTML format that tells of any backups that failed for any server the day before. It works great and I know there is a need. There would have to be adjustment of the query if you are using native backup. Litespeed allows for a central repository and that is what we are using currently.
    DECLARE @xml NVARCHAR(MAX)
    DECLARE @body NVARCHAR(MAX)


    SET @xml =CAST(
    (
    select
    a.ServerName as 'td','',
    d.DatabaseName as 'td','',
    d.Deleted as 'td','',
    t.ActivityTypeName as 'td','',
    a.StartTime as 'td','',
    a.FinishTime as 'td','',
    st.statusname as 'td','',
    a.DatabaseSize as 'td','',
    a.PercentCompleted as 'td','',
    a.ActivityDetail as 'td','',
    a.ErrorMessage as 'td'
    from
    LiteSpeedActivity a
    inner join
    LitespeedDatabase d ON
    a.databaseid = d.databaseid
    anda.servername = d.servername
    inner join
    LitespeedActivityType t ON
    t.activityTypeID = a.activitytypeid
    inner join
    LitespeedStatusType st ON
    a.statustypeid = st.statustypeid
    where a.finishtime > getdate() - 1
    and statusname <> 'completed'

    FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;


    DECLARE @tableHTML NVARCHAR(MAX) ;

    SET @tableHTML =
    N'<H1>The Big Mamoo </H1> <body bgcolor=#FFD9C8> ' +
    N'<table border="2">' +

    '<tr><th>ServerName</th>
    <th>DatabaseName</th>
    <th>Deleted</th>
    <th>ActivityTypeName</th>
    <th>StartTime</th>
    <th>FinishTime</th>
    <th>statusname</th>
    <th>ActivityDetail</th>
    <th>ErrorMessage</th>' +
    CAST ( ( SELECT
    td = a.ServerName,'',
    td = d.DatabaseName,'',
    td = d.Deleted,'',
    td = t.ActivityTypeName ,'',
    td = a.StartTime,'',
    td = a.FinishTime,'',
    td = st.statusname,'',
    td = a.ActivityDetail,'',
    td = a.ErrorMessage
    from
    LiteSpeedActivity a
    inner join
    LitespeedDatabase d ON
    a.databaseid = d.databaseid
    anda.servername = d.servername
    inner join
    LitespeedActivityType t ON
    t.activityTypeID = a.activitytypeid
    inner join
    LitespeedStatusType st ON
    a.statustypeid = st.statustypeid
    where a.finishtime > getdate() - 1
    and statusname <> 'completed'

    FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;


    EXEC msdb.dbo.sp_send_dbmail

    @recipients =N'me@myisp.com;
    @body = @tableHTML,
    @body_format ='HTML',
    @subject ='Backups',
    @profile_name ='MyMailserverProfileName'


    Let me know if you find it handy!
    MichaelB, Feb 16, 2010
    #2

  2. satya Moderator

    Hey MikeThis is interesting and it looks good if you can post script in native mode than running mode. I learn a new concept today.
    satya, Feb 21, 2010
    #3

Share This Page