What are the challenges you face when working across database platforms? Take the survey

Step 1: Log backup with copy (Error 50000)

Carl CorwinCarl Corwin Posts: 8
edited March 14, 2007 10:52AM in SQL Backup Previous Versions

I'm using a vbscript that generate a SQL Agent for LogShipping using SQL Backup. The agent created is : SQL Backup log shipping <database>
There's only one step into the agent.
Here's the T-SQL code :

DECLARE @success int
DECLARE @datestamp varchar(30)
DECLARE @backupcmd varchar(512)
SET @datestamp = left(replace(replace(replace(convert(varchar(30), getdate(), 120), '-', ''), ' ', ''), ':', ''), 12)
SET @backupcmd = '-SQL "BACKUP LOG [mydatabase] TO DISK=''<AUTO>'''+
' WITH COMPRESSION=1, NAME=''Database mydatabase, (log) '+@datestamp +
''', DESCRIPTION=''Database mydatabase, (log) '+@datestamp+''', COPYTO=''\\networkserver\SQL\Log'', ERASEFILES=1" -I myinstance'
EXEC @success=master..sqlbackup @backupcmd
IF @success=0
RAISERROR('Backup failed for database mydatabase', 16,1)

When I look in the agent history of SQL 2005, all entry for that agent are in error with SQL Severity 16 and SQL Message ID 50000.
But the process did occur. Backup of log was done, copy to network folder and the 2nd server did restore the log.

Since I'm using monitoring tools on the Agents, it keeps poping up with that Agent error code. What is the cause of that error message and how can I fix it ?

Thanks in advance. :)


  • Options
    peteypetey Posts: 2,358 New member
    Extended stored procedures by convention return 0 when reporting a successful run and 1 when reporting an error (e.g. http://msdn2.microsoft.com/en-us/library/aa197372(sql.80).aspx).

    You might want to use the exit codes and SQL error codes that can also be returned by SQL Backup, to better identify the nature of the error that occurred.

    DECLARE @exitcode int
    DECLARE @sqlerrorcode int
    EXEC master..sqlbackup N'-SQL "BACKUP DATABASE ..." ', @exitcode OUTPUT, @sqlerrorcode OUTPUT
    IF &#40;@exitcode &lt;&gt;0&#41; OR &#40;@sqlerrorcode &lt;&gt; 0&#41;
      RAISERROR &#40;'SQL Backup job failed with exitcode: %d  SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode&#41;
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Hi Petey,

    Thanks for the prompt response !

    I've try your change and here's the result :
    SQL Backup job failed with exitcode: 870 SQL error code: 0 [SQLSTATE 42000] (Error 50000)

    I've search the red-gate forum and this exitcode relate to the lenght of the database name. Our database name is 14 caracters long so it's not the case.

    Every 15 minutes, the agent create 2 files like thoses :
    20070313 134740 01 {7D2B07A5-009C-4E05-80F3-98BC79EBD7E2}.log

    The file .log file contains :
    SQL Backup log file
    2007-03-13 1:47:37 PM: Backing up mydatabase (transaction log) on myinstance instance to:

    2007-03-13 1:47:37 PM: BACKUP LOG i]mydatabase[/i TO DISK = 'D:\SQL-Prod\LogShipping\LOG_myinstance_mydatabase_20070313_134737.sqb' WITH NAME = 'Database mydatabase, (log) 200703131347', DESCRIPTION = 'Database mydatabase, (log) 200703131347', ERASEFILES = 1, COPYTO = '\\my2ndserverinstance\SQL-BI\LogShipping', COMPRESSION = 1

    2007-03-13 1:47:38 PM: Backup data size : 16.313 MB
    2007-03-13 1:47:38 PM: Compressed data size: 4.625 MB
    2007-03-13 1:47:38 PM: Compression rate : 71.65%

    Processed 1978 pages for database 'mydatabase', file 'mydatabase_Log' on file 1.
    BACKUP LOG successfully processed 1978 pages in 0.228 seconds (71.069 MB/sec).
    2007-03-13 1:47:40 PM: Copied D:\SQL-Prod\LogShipping\LOG_myinstance_mydatabase_20070313_134737.sqb to \\my2ndserverinstance\SQL-BI\LogShipping\LOG_myinstance_mydatabase_20070313_134737.sqb.
    2007-03-13 1:47:40 PM: SQL Backup process ended.
    So it proved that the agent did execute correctly but sqlbackup had return 1 to the @success int and consequently the History of the Agent is with a Job Error !

    Any idea what's causing the exitcode :?:
  • Options
    peteypetey Posts: 2,358 New member
    Exit code 870 indicates that no command was passed to SQL Backup e.g.
    DECLARE @exitcode INT
    EXEC master..sqlbackup '', @exitcode OUTPUT
    SELECT @exitcode
    Is the job passing an empty command somewhere?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Hi Petey,

    You're right. My mistake ! ! ! :(

    I've modified the Agent EXEC line, as you've provide, like this :

    EXEC master..sqlbackup @backupcmd, @exitcode OUTPUT, @sqlerrorcode OUTPUT

    and everything works fine now. No more error. All success ! ! !

    Thanks for your help, greatly appreciated ! ! ! :D
Sign In or Register to comment.