What all files are required for restoration process of SQL Server in Full + Diff + Transactional backup policy

Question

I am reading about different types of backup policy in sql server. I am not clear with backup policy for Full + Differential + Transactional backiup

My main attention is towards what all files would be required at the time of restoration. As per the diagram given on SQL Authority, it seems like I need all the files i.e.

last full backup file 1 + log file 1.1 + log file 1.2 + differential 1.1 + log file 1.4 + log file 1.5 + Differential 1.2 + ...all log files..

or did i interpret it wrong and I would need last full backup file + differential 2 + log file 2.1 + log file 2.2....

EDIT:

What is contained in differential backup, is it just mdf + ndf backup or ldf + mdf + ndf backup since last full backup was taken?

Answer 1

For example, we have the database "test" and we make a full backup every 24 hours, differential backup every 6 hours and backup your transaction log every hour. But for this schedule your database should run with full or bulk-logged recovery model. So, what should we do when the failure occurs? First of all, we have to restore our last full backup. Full database backup consists of a backup copy that containing all data files and active part of the transaction log.

RESTORE DATABASE test FROM DISK = 'd:/test.bak' WITH NORECOVERY, REPLACE

The Next step is to restore the last differential database backup, that containing all changes made to the database since the last full backup and active part of the transaction log.

RESTORE DATABASE test FROM DISK = 'd:/test_diff.bak' WITH NORECOVERY

And finally we can restore all transaction log backups. Transaction log backup contains all log records that haven’t been backed up, up to the last log record that exists at the time of backup completion. We have to restore all transaction log backups that have been made after the last differential backup according to their log sequence number.

RESTORE LOG test FROM DISK = 'd:/test_log59.bak' WITH NORECOVERY

RESTORE LOG test FROM DISK = 'd:/test_log60.bak' WITH NORECOVERY

And the last one

RESTORE LOG test FROM DISK = 'd:/test_log61.bak' WITH RECOVERY

Answer 2

Restoring the backup goes in the sequence of

• Last Full Backup
• Last Differential since last Full Backup
• All Transactions logs since last Differential

The simplest way to view the differential (although this isn't exactly what it is) is a bulk transaction log

You'll usually keep all the things in between to do a point in time restore if a problem was introduced at some point along the way you can restore the system to before it happened.

So while you may have -

Full 1
--Trans 1.0.1
--Trans 1.0.2
-Dif 1.1
--Trans 1.1.1
--Trans 1.1.2
-Dif 1.2
--Trans 1.2.1
--Trans 1.2.2

It means that if something is wrong with the Latest diff backup you can restore up to Trans 1.1.2 without loosing too much data if you see the above table think of your restore order as the point you want to restore to (so for the latest in that list 1.2.2 you require Full backup 1, Differential Backup 2 (remember you need all transactional logs though)

Hope that helps clear it up

Ste

Answer 3

or did i interpret it wrong and I would need last full backup file + differential 2 + log file 2.1 + log file 2.2....

I have not read the blog but above one seems correct approach. Differential backup is mainly there to reduce Recovery Time Object(RTO) it reduces the time taken to bring database up and running after disaster strikes.

Example:

If you have

1. Daily full backup
2. Every 4 hr differential backup
3. Every 15 mins log backup

Suppose your database crashes today at 5 PM.

The restore sequence would be

First restore Today's full backup with no recovery then 4 PM differential backup with no recovery and the all log backup with no recovery, just the last log backup with recovery to bring database online.

If in above scenario you dont take diff backup then restore sequence would be

Todays full backup and then all log backups taken after this full backup witn no recovery just the last backup with recovery to bring DB online.

What is contained in differential backup, is it just mdf + ndf backup or ldf + mdf + ndf backup since last full backup was taken?

You added above part of question after my answer so I will include this as well.Differential backup includes are all the data extents that have changed since the last full backup. Yes they also include (if required) few amount of transaction log to bring database into consistent state after restore is done. For more details read this