how can print or mail error description in Backup t-sql

• zsafakhah

  SSC-Addicted

  Points: 441

  More actions

  June 5, 2012 at 3:58 am

  #261960

  hello dears,

  i want create a backup file with t-sql command and if error raised send mail with error description:

  BACKUP DATABASE @name TO DISK = @fileName

  if (@@ERROR <> 0)

  Begin

  send-mail errordescription

  end

  how can do this?

• Geoff A

  SSChampion

  Points: 11511

  More actions

  June 5, 2012 at 6:45 am

  #1497011

  you could do this;

  BACKUP DATABASE @name TO DISK = @fileName

  if (@@ERROR<>0)

  begin

  create table tempdb..errorlog (LogDate datetime, ProcessInfo varchar(100), [Text] varchar(max))

  insert into tempdb..errorlog

  exec sys.xp_readerrorlog 0

  declare @mailcmd varchar(1000)

  set @mailcmd = (select top 1 [Text] from tempdb..errorlog

  where ProcessInfo = 'Backup' and [Text] like 'BACKUP failed%'

  order by LogDate desc)

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'DB_Mail', -- you will need to change this.

  @recipients = 'DBA_Super_Hero@mymailserver.com', -- you will need to change this.

  @body = @mailcmd,

  @subject = 'Your backup failed!' ;

  drop table tempdb..errorlog

  end