1

I am studying differential backups. I know that I can create a differential backup which will contain all the modified data from last full backup. I wonder isn't it possible to create a backup from the last differential backup? I mean to create a base full backup, then differential backup1, which contains all the data modified from full backup, then create backup2 which contains modified data from backup1, not full backup.

If it is possible how can I do it? Also can I do the same only with Log file backups?

Thanks for your time.

2

SQL Server differential backups capture all of the LSNs from the last full backup in the backup chain. Log backups capture all of the LSNs from the last log backup. If there has not been a log backup since taking the full backup, the log backup captures the LSNs from that one. Considering this, the log backups better relate to "incremental".

  • 1
    Log backups capture all of the log records since the last log backup, regardless of whether there has been a full backup taken. Full Backups never break the chain, and you can restore from an older Full backup and all the log backups. – David Browne - Microsoft Aug 3 '18 at 18:06
2

You are essentially asking for incremental, which is pretty much what a log backup is. So your Diff1 and Diff2 would actually be log backups, which still gets you point in time restores.

Why are you trying to do this...purely academic reasons?

  • 1
    In Oracle you do this beside transaction log backup. Differentials will hold changes from last differential but not in SQL Server. – SqlWorldWide Aug 3 '18 at 15:48

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.