Have SQL email you if your Litespeed (or native) backups fail | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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

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!

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.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |