Step 1: Log backup with copy (Error 50000)
Carl Corwin
Posts: 8
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.
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.
Comments
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:
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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 : 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 :?:
Is the job passing an empty command somewhere?
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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 ! ! !