sql-serverbackuptransaction-loglog-shipping

Transaction log shipping together with backup job = conflict?


There is already a backup job plan that runs every 15 minutes. I created transaction log shipping process but after 1 or two restored logs this job stops working (error: it can't find matching log). Obviously, there are logs created by two individual jobs and for the log shipping process only part of logs are being copied to secondary database (log created by backup job plan are excluded). Does this mean that I will have to turn off backup job plan?


Solution

  • The log shipping process does its work by taking log backups. There cannot be another job that does the same, it will break the log backup chain. See Using Log Shipping as Part of a Recovery Plan. Your recovery strategy should clearly document the location of the log backups as taken by the log shipping job and detail step-by-step instructions how to recover the database using these log backups. You should also test this strategy to validate it.

    BTW, right now your backup chain is busted as it misses log. You need to take a full database backup to re-seed the chain properly.