Hi,
i have set up databasemail to send me e-mail after my sql backup has finished.
Profile and account has bin configured,and test mail ha been sent. Works just fine.
But when my backup has finished, no e-mail is being sent.
Get this error message in error logs: [264] An attemtemt was made to send an e-mail when no email session was established.
My system is
Microsoft Windows Server 2003 R2. SP2. Standard Edition.
SQL sever 2005, sp2
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 6.0.3790.3959
Microsoft .NET Framework 2.0.50727.832
Operating System 5.2.3790
DId you have a look on:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=354995&SiteID=1
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||You can use the below code as step two in your backup job to invoke mail alert.Dont forget to change profilename,recipients, subject & body in the below code before inserting into job
Code Snippet
Exec msdb..sp_send_dbmail @.profile_name='<profilename>',
@.recipients='Your Email Address',
@.subject='Your Subject here',
@.body='Your Body Message',
@.query='SET NOCOUNT ON
select ''SERVER NAME : '' + @.@.servername
select SUBSTRING(s.name,1,40) AS ''Database Name'',
CAST(b.backup_start_date AS char(25)) AS ''Last Backup Date'',
CASE WHEN b.backup_start_date > DATEADD(dd,-1,getdate())
THEN ''Backup Completed ''
WHEN b.backup_start_date > DATEADD(dd,-7,getdate())
THEN ''Backup Older than One day ''
ELSE ''Backup Older than One Week ''
END
AS ''Status'',
substring(m.physical_device_name ,1,100) AS ''Backup File Name''
from master..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date = (SELECT MAX(backup_start_date)
FROM msdb..backupset
WHERE database_name = b.database_name
AND type = ''D'' ) -- full database backups only, not log backups
left outer join msdb..backupmediafamily m
on m.media_set_id=b.media_set_id
and m.physical_device_name=(select max(physical_device_name) from msdb..backupmediafamily
where media_set_id=b.media_set_id)
WHERE s.name <> ''tempdb''
ORDER BY s.name'
