Database Backups – Full vs Differential vs Incremental

dbbackup10-png

This article mainly discuss about the differences between Full Database Backup, Differential  and Incremental Database Backup.

Choose the right backup method

Here is a quick summary of the most common backup methods available:

Full Backup captures your entire system and all the data you want to protect. Frequent full backups result in easier recovery operations.

  • Pros: Fast, reliable recovery
  • Cons: Slower backup, lots of storage required

Incremental Backup captures only the changes made since the last incremental backup. Saves both time and storage space, and ensures that your backup is up to date.

  • Pros: Fast backups, low storage space
  • Cons: Slower recovery, risk of data loss

Differential Backup captures only the changes made since the last full backup, not since the last differential backup. This requires more storage space, but ensures an easier, more reliable restore. Many organizations struggle with incremental vs. differential backup solutions.

  • Pros: Fast backups, low storage space
  • Cons: Slower recovery

Where To Store DB Backup’s?

Once you’ve created a backup, where is the best place to store it safely? Here are the most common options:

External Hard Drive

  • Portable and comparatively less expensive
  • Widely used storage media

Cloud Storage

  • Secure offsite storage
  • Files remain safe & accessible at any time
  • Most reliable & convenient for business data

Network Attached Storage (NAS)

  • Uses NAS device
  • Accessible from anywhere on your network

Let’s See How to Do it

Phase I: DB Backup

The following DB script shows creating a Database named ‘Abraham DB’ and further creating Table and Rows in it. During each stage we take the DB Backups to understand how it can do and how to restore when it is required.

CREATE DATABASE AbrahamDB
 GO
 USE AbrahamDB
 GO
 CREATE TABLE Table1 (id INT, name varchar(250))
 GO
 BACKUP DATABASE AbrahamDB TO DISK = 'C:\Temp\DBBackUp\FULL_Backup1.bak'
 GO
 INSERT INTO Table1 VALUES (1,'A')
 GO
 BACKUP DATABASE AbrahamDB TO DISK = 'C:\Temp\DBBackUp\DIFF_Backup1.bak' WITH DIFFERENTIAL
 GO
 INSERT INTO Table1 VALUES (2,'B')
 GO
 BACKUP DATABASE AbrahamDB TO DISK = 'C:\Temp\DBBackUp\DIFF_Backup2.bak' WITH DIFFERENTIAL
 GO
 INSERT INTO Table1 VALUES (3,'C')
 GO
 BACKUP DATABASE AbrahamDB TO DISK = 'C:\Temp\DBBackUp\FULL_Backup2.bak'
 GO
 INSERT INTO Table1 VALUES (4,'D')
 GO
 BACKUP DATABASE AbrahamDB TO DISK = 'C:\Temp\DBBackUp\DIFF_Backup3.bak' WITH DIFFERENTIAL
 GO

DBBackup.png

Let us analyze the results,

DBBackup1.png

The different stages of Backup are showed below:

DBBackup3.png

Phase II: DB Restore

I hope so far it is clear to understand. Now we will try to Restore this DB from the  different stages we took the Backups before.

USE MASTER
 GO
 RESTORE DATABASE AbrahamDB FROM DISK = 'C:\Temp\DBBackUp\FULL_Backup1.bak' WITH NORECOVERY
 GO
 RESTORE DATABASE AbrahamDB FROM DISK = 'C:\Temp\DBBackUp\DIFF_Backup1.bak' WITH NORECOVERY
 GO
 RESTORE DATABASE AbrahamDB FROM DISK = 'C:\Temp\DBBackUp\DIFF_Backup2.bak' WITH NORECOVERY
 GO

The output would be the below:

DBBackup5.png

The reason because the last Restore Database command should use WITH RECOVERY instead of WITH NORECOVERY

Re-running the same with last command as WITH RECOVERY and the output would be

USE MASTER
 GO
 RESTORE DATABASE AbrahamDB FROM DISK = 'C:\Temp\DBBackUp\FULL_Backup1.bak' WITH NORECOVERY
 GO
 RESTORE DATABASE AbrahamDB FROM DISK = 'C:\Temp\DBBackUp\DIFF_Backup1.bak' WITH NORECOVERY
 GO
 RESTORE DATABASE AbrahamDB FROM DISK = 'C:\Temp\DBBackUp\DIFF_Backup2.bak' WITH RECOVERY
 GO

DBBackup4.png

Now trying to restore from the last differential backup :

USE MASTER
 GO
 RESTORE DATABASE AbrahamDB FROM DISK = 'C:\Temp\DBBackUp\FULL_Backup2.bak' WITH NORECOVERY
 GO
 RESTORE DATABASE AbrahamDB FROM DISK = 'C:\Temp\DBBackUp\DIFF_Backup3.bak' WITH RECOVERY
 GO

DBBackup6.png

 

Points to Remember:

  1. You need to use the Last Restore command with RECOVERY instead of NO RECOVERY
  2. You can restore the differential from the Full back of its Parent only. eg: Diff_Backup3.bak can Restore into its Parent Fullbackup i.e Full_Backup2.bak similarly DIFF_backup1.bak and DIFF_Backup2.bak can be Restored to its ParentFull backup i.e FULL_Backup1.bak only.DBBackup7.png

Hope it is clear. Happy Programming.

%d bloggers like this: