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 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

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






5 comments:

  1. Shall we use attach data file instep of restore backups

    ReplyDelete
    Replies
    1. Attach data file is also good option but when you are migrating database. In all other cases i prefer Backups to avoid any down time.

      Delete
  2. Hi Saurabh,

    Which version you 've used for this test?

    ReplyDelete
    Replies
    1. Hi Rodrigo

      This is not a new feature. I have tested it in SQL 2008R2 / SQL 2012 / SQL 2014 and i am sure it will be fine in 2005 also.

      Delete