0

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?

1
  • Don't use COPY_ONLY - you need to back up the log in a way that releases the VLF's for reuse. Alternately, explain the business purposes behind your 'requirement' and precisely what you mean by incremental and cumulative, as well as your requirements for Point In Time Restore and for VLF reuse in the transaction log. From the sounds of the question so far, you're trying to do something highly unusual and likely not recommended. Commented Jan 14, 2018 at 9:26

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.