SQL Server incremental Log backups

Question

We are taking COPY_ONLY (non-truncating) transaction log backups every 2 hours. Our requirement is to take the incremental log backup every 2 hours and not cumulative. I did not find an option in BACKUP LOG TSQL command. Is there a way to do this?

Answer 1

We are taking COPY_ONLY (non-truncating) transaction log backups every 2 hours.

Reference: Copy-Only Backups (SQL Server)

You need to understand the difference between COPY_ONLY and without COPY_ONLY?

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose. +

A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create a new routine log backup (using WITH NORECOVERY) and use that backup together with any previous log backups that are required for the restore sequence. However, a copy-only log backup can sometimes be useful for performing an online restore. For an example of this, see Example: Online Restore of a Read-Write File (Full Recovery Model).

Our requirement is to take the incremental log backup every 2 hours and not cumulative.

Schedule transaction log backup every 2 hours. Make sure your business stack holders understand the consequence of taking transaction log backup every 2 hours.

Read this answer (What's a good SQL Server backup schedule?) for more a better understanding. Here is (Transaction Log Backups (SQL Server) ) Microsoft's official standing on this.

I did not find an option in BACKUP LOG TSQL command. Is there a way to do this?

There is enough example at the bottom of this page.

BACKUP (Transact-SQL)

As a side note ( I am advocating this) many Database professionals use Ola Hallengren's solution.

https://ola.hallengren.com/sql-server-backup.html