How to trap failed backup command?

• ckempste

  SSCoach

  Points: 17983

  More actions

  July 8, 2001 at 11:43 am

  #104478

  Hi all

  I am whipping up my own script for backups (diff, full and log) with emailing, compression and deletion of files X days long etc (I will post when complete).

  I am having problems trapping the BACKUP command on failure... ie:

  set @v_starttime = getdate()

  BACKUP DATABASE @p_database TO vxtmpdump WITH INIT, NAME = @p_database, NOSKIP , STATS = 10, DESCRIPTION = @v_filename, NOFORMAT

  if @@error > 0 then

  <etc>

  does not work, actually the script stops immediatly at the failure of the command. Is the only way to trap this error is by scheduling it as a job?

  Cheers

  Chris

  
  Chris Kempster
  www.chriskempster.com
  Author of "SQL Server Backup, Recovery & Troubleshooting"
  Author of "SQL Server 2k for the Oracle DBA"

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736173

  More actions

  July 8, 2001 at 11:04 pm

  #416965

  You might try trapping using DTS. Enter this command in a T-SQL step and then setting succes and failure steps.

  Steve Jones

  steve@dkranch.net