SQL Server natively do not support non-cumulative differential backup type. I wonder why independent backup software providers like from RedGate, Idera or Quest do not support it either? It looks easy to implement: just store LSN from last non-cumulative backup and put in the backup files only pages changed from this LSN and could be useful for log shipping databases in simply recovery mode.

Is there any caveat I am missing?

  • 2
    I believe a non-cumulative differential backup would be an incremental backup. In SQL Server, an incremental back up is done via a transaction log backup. SQL Server natively handles this. Third party apps will also support this. – Chris Lumnah Nov 23 '20 at 16:52
  • 2
    I think the simple answer is most people who care about their data use full recovery + t-log backups, if simple is your requirement then take more fulls. – Aaron Bertrand Nov 23 '20 at 16:53
  • What you are proposing is perhaps too similar to a transaction log backup - from a development standpoint a lot of extra complexity for relatively little net benefit – Stephen Morris - Mo64 Nov 24 '20 at 13:00

Transaction log backups are incremental, that is, they only store incremental changes since the previous transaction log backup. And they do it more or less as you told, using last well know backuped LSN (a checkpoint).

This is why you have to restore the full backup and then all the TLOG backups in the proper order.

But this requires full recovery model. Differential backups work in all recovery models, namely Simple, Bulk-Logged, and Full.

If you want incremental backups (i.e. non-cumulative differential backup), take transaction log backups regularly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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