Skip to content

SQL Server Differential Backups

    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')
    
    
    Take full database backup of six rows
    
    BACKUP DATABASE [BackupDatabase] 
    TO  DISK = N'c:\fullbackups\BackupDatabase.bak' 
    WITH NOINIT,  --<< No override
    NAME = N'BackupDatabase-Full Database Backup', 
    STATS = 10
    GO
    
    --1
    Insert into Products values ('Doll','Toy_R_us')
    
    BACKUP LOG BackupDatabase 
    TO  DISK = N'c:\fullbackups\BackupDatabase.bak' 
    WITH 
    NAME = N'BackupDatabase-Transaction Log  Backup', 
    STATS = 10
    GO
    
    --2
    Insert into Products values ('House','Remax')
    
    BACKUP LOG BackupDatabase 
    TO  DISK = N'c:\fullbackups\BackupDatabase.bak' 
    WITH 
    NAME = N'BackupDatabase-Transaction Log  Backup', 
    STATS = 10
    GO
    
    --3
    Insert into Products values ('Car','Porche')
    
    BACKUP DATABASE [BackupDatabase] 
    TO  DISK = N'c:\fullbackups\BackupDatabase.bak' 
    WITH  DIFFERENTIAL , 
    NOINIT,  
    NAME = N'BackupDatabase-Differential Database Backup',   
    STATS = 10
    GO
    
    --4
    Insert into Products values ('Chair','Walmart')
    
    BACKUP LOG BackupDatabase 
    TO  DISK = N'c:\fullbackups\BackupDatabase.bak' 
    WITH 
    NAME = N'BackupDatabase-Transaction Log  Backup', 
    STATS = 10
    GO
    
    --5
    Insert into Products values ('Mouse','Apple')
    
    BACKUP LOG BackupDatabase 
    TO  DISK = N'c:\fullbackups\BackupDatabase.bak' 
    WITH 
    NAME = N'BackupDatabase-Transaction Log  Backup', 
    STATS = 10
    GO
    
    --6
    Insert into Products values ('TV','Sony')
    
    BACKUP DATABASE [BackupDatabase] 
    TO  DISK = N'c:\fullbackups\BackupDatabase.bak' 
    WITH  DIFFERENTIAL , 
    NOINIT,  
    NAME = N'BackupDatabase-Differential Database Backup',   
    STATS = 10
    GO
    
    --7
    Insert into Products values ('Phone','Apple')
    
    BACKUP LOG BackupDatabase 
    TO  DISK = N'c:\fullbackups\BackupDatabase.bak' 
    WITH 
    NAME = N'BackupDatabase-Transaction Log  Backup', 
    STATS = 10
    GO
    
    Look insde the .bak file
    RESTORE HEADERONLY FROM DISK = N'c:\fullbackups\BackupDatabase.bak'
    
    SELECT * FROM Products --13 ROWS
    
    INSERTS HAPPENED HERE BUT NOT BACKUP
    
    Insert into Products values ('DESK','IKEA')
    Insert into Products values ('LAMP','SUMMERS')
    Insert into Products values ('PENS','BIGGS')
    Insert into Products values ('PILLOW','SAMS')
    
    SELECT * FROM Products --17 ROWS
    
    DATABASE IS CORRUPT!  NEED TO RESTORE ASAP  (HOW DO YOU GET THE LAST INSERTS)
    TAKE A TAIL LOG IF POSSIBLE!!
    
    --AT THIS POINT, IF I WERE TO RESTORE THE DATABASE WITH POSTION 1, 7 AND 8, I WOULD RETRIVE 13 ROWS, NOT 17.  THUS I NEED TO TAKE TAIL LOG BACKUP IF POSSIBLE
    
    Look insde the .bak file
    RESTORE HEADERONLY FROM DISK = N'c:\fullbackups\BackupDatabase.bak'
    
    BEFORE RESTORING THE DATABASE, TAKE A LAST TAIL-LOG BACKUP IF POSSIBLE WITH NORECOVERY OPTION TO GRAB THE LAST FOUR INSERTS
    
    use master
    go
    
    BACKUP LOG [BackupDatabase] 
    TO  DISK = N'c:\fullbackups\BackupDatabase.bak' 
    WITH  NO_TRUNCATE ,  --<< must have the NO_TRUNCATE option set, so as not to delete the remaining four inserts
    COPY_ONLY,           --<< also, have the option COPY_ONLY so as not to break the chain of sequential t-logs of the backup set
    NOFORMAT, NOINIT,  
    NAME = N'BackupDatabase-Transaction Log  Backup', 
    SKIP, NOREWIND, NOUNLOAD,  
    NORECOVERY ,  STATS = 10 --<< this norecovery option puts the database in to restoring mode
    GO
    
    sp_who kill 54
    
    THEN RESTORE THE DATABASE USING THE FOLLOWING SCRIPT IN ORDER (FULL, LAST DIFF, ALL T-LOGS)
    
    RESTORE DATABASE [BackupDatabase] 
    FROM  DISK = N'c:\fullbackups\BackupDatabase.bak' 
    WITH  FILE = 1,  
    NORECOVERY,  
    NOUNLOAD,  
    STATS = 5
    
    RESTORE DATABASE [BackupDatabase]               --<<last diferential
    FROM  DISK = N'c:\fullbackups\BackupDatabase.bak' 
    WITH  FILE = 7,  
    NORECOVERY,  
    NOUNLOAD,  
    STATS = 5
    
    RESTORE LOG [BackupDatabase] 
    FROM  
    DISK = N'c:\fullbackups\BackupDatabase.bak'
    WITH  FILE = 8,  
    NOUNLOAD, 
    NORECOVERY, 
    STATS = 5
    GO
    
    Tail log restore
    
    RESTORE LOG [BackupDatabase] 
    FROM  
    DISK = N'c:\fullbackups\BackupDatabase.bak'
    WITH  FILE = 9,  
    NOUNLOAD,  
    STATS = 5
    GO

    Also See: