Step 1: Log backup with copy (Error 50000)

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.

Carl Corwin March 13, 2007 10:02

Comments

4 comments

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
```
petey March 13, 2007 11:33

0
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:
\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 \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 :?:

Carl Corwin March 13, 2007 14:10

0
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?
petey March 13, 2007 22:12

0
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 ! ! !

Carl Corwin March 14, 2007 10:52

0

Add comment

Please sign in to leave a comment.

How can we help you today?

Step 1: Log backup with copy (Error 50000)

Comments

Add comment