INCORRECT : "Differential backup can only restore over last full backup" In SQL Server

Awwwwwwww , That’s my first reaction when I tried this. But yes its incorrect to say that differential backup can only restore over last full back.

So first lets discuss How I can say that? , "For Why" I will read more internals and try to give you exact reason

So here’s demo ,

I am creating one database with one table and with every record insert I will take one backup. Order of backup will be

1. Full backup
2. Log backup
3. Full Backup
4. Log backup
5. Differential Backup

So here my order of restore must be 

Full backup (3) , Differential backup (5). But interviewer asked  my full back at (3) is corrupted  So you will say

Full backup (1) , log backup (2) , Log backup (4) and tail log backup which I have done before starting restore.

But I have better plan which would be very helpful if I have log backup at every 30 mins, So my plan is

Full Backup (1) , , log backup (2) , Log backup (4), Differential backup (5)

And I have all my 5 rows in out put.  So here is demo

                                   DEMO

/**** Create Database ****/

Create database BACKUP_Test  

GO

use BACKUP_Test

GO

Create table emp (id int)

GO

Insert into emp values (1)

GO

-- Taking First Full backup

Backup database BACKUP_Test to disk = 'C:\saurabh\Full_BACKUP_Test1.bkp' -- Full

GO

Insert into emp values (2)

GO

-- Taking First log backup

Backup log BACKUP_Test to disk = 'C:\saurabh\Log_BACKUP_Test1.bkp' -- LOG

GO

Insert into emp values (3)

GO

-- Taking Second Full backup

Backup database BACKUP_Test to disk = 'C:\saurabh\FULL_BACKUP_Test2.bkp' -- Full

GO

Insert into emp values (4)

GO

-- Taking Second log backup

Backup log BACKUP_Test to disk = 'C:\saurabh\LOG_BACKUP_Test2.bkp' -- LOG

GO

Insert into emp values (5)

GO

-- Taking First Differential backup

Backup database BACKUP_Test to disk = 'C:\saurabh\Differential_BACKUP_Test1.bkp' with differential -- DIff

GO

-- So how many rows I have? -- 5

Select * from emp -- 5

GO

/**** Lets have a look on Backup history of database BACKUP_Test ****/

select b.database_name, b.type, bf.physical_device_name, b.backup_start_date, b.backup_finish_date

from msdb..backupset b left outer join msdb..backupmediafamily bf on bf.media_set_id = b.media_set_id

where  b.database_name = 'BACKUP_Test' order by b.backup_start_date desc

GO

/**** Output of Backup History ****/

So our last backup was our only differential backup and we are going to ignore 2^nd full backup

/**** Taking Tail backup else database will not let me restore ***/

use master

GO

Backup log BACKUP_Test to disk = 'C:\saurabh\Log_BACKUP_Test3.bkp' with NOrecovery -- log

GO

/**** Lets now restore database with full, log, log and last is differential backup ****/

-- Restore Full backup 1

Restore database BACKUP_Test from disk = 'C:\saurabh\Full_BACKUP_Test1.bkp' with norecovery 

GO

-- Restore T-log Backup 1

Restore log BACKUP_Test from disk = 'C:\saurabh\Log_BACKUP_Test1.bkp' with norecovery

GO

-- Restore T-log Backup 2

Restore log BACKUP_Test from disk = 'C:\saurabh\LOG_BACKUP_Test2.bkp' with norecovery

GO

-- Restore differential Backup 1

Restore database BACKUP_Test from disk = 'C:\saurabh\Differential_BACKUP_Test1.bkp' with norecovery

GO

-- Recovering database

Restore database BACKUP_Test with  recovery

/**** lets check if our dataabse have restored correct data .i.e 5 rows ****/

GO

USE BACKUP_Test

GO

Select * from emp

GO

So 

• I can use differential backup over log backup
• I can use log backup over full backup other than last full backup

/**** Finally lets cleanup test environment by dropping database ****/

Drop database BACKUP_Test

GO