Step 1: Log backup with copy (Error 50000)

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

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
BEGIN
RAISERROR('Backup failed for database mydatabase', 16,1)
END

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. :)
· Share on Twitter

Comments

  • 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.

    E.g: 
    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;
BEGIN
  RAISERROR &#40;'SQL Backup job failed with exitcode: %d  SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode&#41;
END
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • Carl CorwinCarl Corwin Posts: 8
    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
    and
    LOG_myinstance_mydatabase_20070313_134737.sqb

    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:
    D:\SQL-Prod\LogShipping\LOG_myinstance_mydatabase_20070313_134737.sqb

    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 :?:
    · Share on Twitter
  • 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
    · Share on Twitter
  • Carl CorwinCarl Corwin Posts: 8
    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
    · Share on Twitter
Sign In or Register to comment.