The following code needs to be inserted in the Transaction Log Backup routine just before the check to see if bolProcessDB is true (indicating we're going to add the steps to backup the transaction log for the current database):


' Log Shipping generates its own set of transaction log backups, and doing log backups
' outside of that process would break the chain of log backup files used by the remote
' databases, so we do NOT want to backup transaction logs for databases that are using
' log shipping.
If bolProcessDB = True Then
Dim jobLogShipBackup As Job
jobLogShipBackup = srvMgmtServer.JobServer.Jobs("LSBackup_" & dbDatabase.Name)
If Not jobLogShipBackup Is Nothing Then ' A Log Ship backup job exists for this database
bolProcessDB = False ' so do not include it in the transaction log backup
End If
End If

With this code, the database won't be processed, so there's no break in the log backup chain for log shipping.

Allen

Just one problem with the above, and that's xp_delete_file. It's notoriously flakey and as of the current release of SQL Server 2005 SP2(a), you simply can't rely on it to delete your old backup files. It will run and report success, but won't delete anything. Search +xp_delete_file +problem on any search engine, it's a royal pain...

any one got the same problem,, even if SP2 it won;t work..

I have a problem with deleting the old backups first. If a database becomes 'suspect' the backup will fail. In that case you need a backup for a restore. If you do not notice the problem in time, the old backups may be gone!

Also I noticed in 'BuildNotifyStep' a small error in the version check. You should use 'xp_smtp_sendmail' with version 7 or 8, not 9.

Perhaps you could give the application a return code to catch by the SQL agent, in case of an error..

it's true...  i've tested this many times in 9.00.3042.00  SP2  and the behavior still exists.

i've seen many versions of this procedure; and is still random results. 

i dont' recommend using it at all.

it's also a 2000 left-over; so eventually MS will probably have it depricated.

beware