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
- Full backup
- Log backup
- Full Backup
- Log backup
- 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 2nd 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
- 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
Shall we use attach data file instep of restore backups
ReplyDeleteHi Saurabh,
ReplyDeleteWhich version you 've used for this test?