When deploying a Backup Template as a job that has “Exclude Offline Databases” unchecked on certain systems, if that job also encounters an offline database during execution of the job, it will fail the job right away rather than continue backing up the other databases before failing like it does when deployed as a Maintenance Plan.
The backup job will terminate after encountering its first error when iterating through the backup template generated list of designated servers.
Microsoft SQL Server 2005 Defect: When you run a Transact-SQL script in a step of a SQL Server Agent job in SQL Server 2005, the result of the Transact-SQL script may be truncated.
Microsoft SQL Server 2000 Defect: A call to a RAISERROR statement in a Transact-SQL batch can cause a SQLAgent job to fail and to lose output in SQL Server.
Here are the steps needed to resolve this issue:
1. Install Microsoft patch via one of these installs:
SQL 2005( pre- SP2) - http://support.microsoft.com/kb/955695
SQL 2000 (pre-Final Service Pack) - http://support.microsoft.com/kb/309802
OR these patches are included in any of the following Microsoft releases:
SQL 2008 R2 (no separate patches available for SQL 2008)
SQL 2005 SP2
The last SQL 2000 Service Pack
2. On SQL 2000, 2005, or 2008 servers that are patched as needed, create a new registry DWORD called “TruncateJobResultOnError” in the instances directory and set to 0.
IMPORTANT NOTE: This registry entry is only usable after ensuring the needed MS patch is properly installed as detailed in Step 1.
Example registry path:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<MSSQL.x>\SQLServerAgent
Note In this registry entry, <MSSQL.x>represents the ID of the instance of SQL Server 2005. You must change it to the correct value for your instance.
3. Restart the SQL Server Agent to activate new registry setting created.
NOTE: For additional help in creating the required DWORD registry entry, please contact Quest Support.
