Help/Ideas to email me when specific error in refresh script happens

• pdavies-598296

  Valued Member

  Points: 74

  More actions

  October 18, 2011 at 9:16 am

  #99491

  I'm looking for some guidance and suggestions as to a script I have running daily. I need it to email me when the database refresh script fails when a specific error occurs. I was thinking of having a simple stored procedure calling sp_send_cdosysmail when either of these two steps fail with the error in the body of the email.

  Here's part of the script I'm looking to watch for issues. The second step has failed due to insufficient free space.

  1.) raiserror('Step 1. Copy Production Backup file over to Sustainment SQL Server',1,1) with nowait

  EXEC master..xp_cmdshell 'COPY \\server2\backup\database1_Full_LiteSpeed.BAK \\server1\backup$\database1_Full_LiteSpeed.BAK'

  2.) raiserror('Step 3. Restore Database to Source SQL Server via lite speed',1,1) with nowait

  exec master.dbo.xp_restore_database

  @database = 'database1,

  @filename = '\\server2\backup$\database1_Full_LiteSpeed.BAK',

  @filenumber = 1,

  @with = 'REPLACE',

  @with = 'RECOVERY',

  @with = 'NOUNLOAD',

  @with = 'STATS = 10',

  @with = 'MOVE N''FS84SYS_Data'' TO N''D:\MSSQL\Data\database1_Data.MDF''',

  @with = 'MOVE N''FS84SYS_Log'' TO N''D:\MSSQL\Data\database1_Log.LDF''',

  @with = 'STATS = 10'

  Appreciate insight.

• handkot

  SSCertifiable

  Points: 5251

  More actions

  October 21, 2011 at 2:00 am

  #1397770

  Use Alert?

  I Have Nine Lives You Have One Only
  THINK!