February 5, 2011 at 12:02 am
I would like to send the email to SQL Admin if backup has not been taken from 2 days.
Email should be not send otherwise.
I have used following format but the message will send every time.
Looking for a quick support on this.
Thanks
EXEC msdb.dbo.sp_send_dbmail
@recipients = email,
@subject = 'Databases Possibly Needing Backups',
@body = 'Attached is the list of databases that may need backups.
',
@attach_query_result_as_file = 0,
@query = 'DaysSinceLastBackup',
@body_format = 'TEXT'
February 5, 2011 at 6:07 am
Run your query to find the # of days and put that in a variable:
set @NumberOfDays = QueryGoesHere
if NumberOfDays >= 2
then
send email
February 5, 2011 at 7:09 am
I would suggest to use a condition like:
if exists (select D.name, B.type as BU_Type, B.backup_finish_date
from master.sys.databases D
left join ( select *
, rank() over ( partition by database_name, type order by database_name, type, backup_finish_date) RNK
from msdb.dbo.backupset
) B
on B.database_name = D.name
and B.RNK = 1
where isnull(B.backup_finish_date, '2000-04-01') < dateadd(dd, -2, getdate())
and D.name not in ('tempdb')
)
begin
your dbmail code over here and use the same query but add an order by clause
end
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 7, 2011 at 9:23 am
i have a process where i dump the backupset tables to a central database and then i have SSRS reports email me this daily.
February 14, 2011 at 3:48 am
just came across this art today at sqlblog : http://sqlblog.com/blogs/davide_mauri/archive/2011/02/13/sys2-scripts-updated-scripts-to-monitor-database-backup-database-space-usage-and-memory-grants-now-available.aspx
Maybe this can help you out.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply