I had two articles published in April on the Simple-Talk web site (Automate your Database Maintenance using SMO and Alert-based Transaction Log Backups - Automate your database Maintenance Part 2), and after they were published a need popped up here requiring me to set up log shipping for a couple of my production databases. Well, my automated transaction log backups would prevent the successful updates via transaction log backups in the log shipping databases, so I added the following code in both programs, within the loop through the databases:
' 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
I added comments after the articles yesterday, but thought I'd reiterate here both the code change and the reason. Note that this code does expect you to use the default naming for the log shipping backup job name. If you use a different naming convention you'll need to modify your program accordingly.
Allen