Skip to content

SQL Server Restore using differential

    Create a test database
    
    Use master
    go
    
    Create database BackupDatabase
    go
    
    use BackupDatabase 
    go
    
    --Create table
    Create table Products
    (ProductID int IDENTITY (1,1) Primary Key,
    ProductName varchar (100),
    Brand varchar (100))
    go
    
    --Insert data into table
    insert into Products values ('motor','chevy')
    insert into Products values ('Hat','Nike')
    insert into Products values ('Shoe','Payless')
    insert into Products values ('Phone','Apple')
    insert into Products values ('Book','Green')
    insert into Products values ('Cup','Large') -- 6 rows (full)
    
    --View data
    select * from Products
    
    --********Take full database backup of six rows using GUI******
    --Look insde the .bak file
    RESTORE HEADERONLY FROM DISK = N'c:\fullbackups\BackupDatabase.bak'
    
    --Insert data into table AFTER a full database backup, but before a transactional log backup
    
    Insert into Products values ('Doll','Toy_R_us') --7 rows (tlog 1)
    
    --View data
    select * from Products
    
    --Taking a transactional log backup (this can only be taken if the database recovery model is in Full mode, and a full database backup had been executed)
    --******Using a GUI (number 1)*******
    --Look insde the .bak file
    RESTORE HEADERONLY FROM DISK = N'c:\fullbackups\BackupDatabase.bak'
    
    --BackupType
    --1 = Full
    --2 = T Log
    --5 = Differential
    
     --Another insert
    Insert into Products values ('House','Remax') --8 rows (first diff)
    
    select * from Products
    
    --*****take first differnetial backup using GUI***** 
    RESTORE HEADERONLY FROM DISK = N'c:\fullbackups\BackupDatabase.bak'
    
    Insert into Products values ('Desk','Ikea') --9 rows (tlog 2)
    
    --another transaction log backup
    --Taking a transactional log backup (this can only be taken if the database recovery model is in Full mode, and a full database backup had been executed)
    --Using a GUI (number 2)
    --Look insde the .bak file
    RESTORE HEADERONLY FROM DISK = N'c:\fullbackups\BackupDatabase.bak'
    
    select * from Products
    
    Insert into Products values ('Desk','Ikea') --10 rows (second diff)
    
    --*****take second differnetial backup using GUI*****
    --Look insde the .bak file
    RESTORE HEADERONLY FROM DISK = N'c:\fullbackups\BackupDatabase.bak'
    
    Insert into Products values ('Book','BAN') --11 rows (tlog 3)
    
    --Taking a transactional log backup (this can only be taken if the database recovery model is in Full mode, and a full database backup had been executed)
    --Using a GUI (number 3)
    Insert into Products values ('Book','BAN') --12 rows (tlog 4)
    
    --Taking a transactional log backup (this can only be taken if the database recovery model is in Full mode, and a full database backup had been executed)
    --Using a GUI (number 4)
    Insert into Products values ('Chips','Lays') --13 rows (tlog 5)
    
    --Taking a transactional log backup (this can only be taken if the database recovery model is in Full mode, and a full database backup had been executed)
    --Using a GUI (number 5)
    --At this point, we have 1 full database backup with data from 1 to 6 rows
    --1 (first) transactional log backup of row 7
    --1 (first) differential backups of row 8
    --2 (second) transactional log backup of row 9
    --2 (second) differential log backup of row 10
    --3 (third) transactional log backup of row 11
    --4 (fourth) transactional log backup of row 12
    --5 (fifth) transactional log backup of row 13
    
    --To restore the database if a disaster occured AND we are using differential backups, we need only the LAST differential plus
    --all the following transactional logs to restore the database! 
    --Lets delete the database and restore the using GUI
    --Look insde the .bak file
    RESTORE HEADERONLY FROM DISK = N'c:\fullbackups\BackupDatabase.bak'

    In order to restore a differential backup, you will first need to restore the last full backup with NO RECOVERY option. So, in SSMS you need to select the appropriate full-backup and choose Restore With NoRecovery option from the Options page as depicted in the following screenshot.

    Once restored, the database will be shown in the Object Explorer as Restoring.

    Notice that the database is non-available/non-functional at this time and is waiting for a differential backup to be applied. Now, restore the appropriate differential backup and choose Restore With Recovery from the Options page:

    That’s it. You have successfully restored a differential backup.

    Also See: