Four SQL Server
backup types
Full, Differential,
Incremental backup and Transaction log backup.
Full backup
A
full backup contains all the data in a database from the creation of the
database till the point of Full Backup initiation. It has enough log information
and all data from file groups or files associated with Database to allow for
recovering that data. It is used as base for differential and transaction log
backup.
Differential backup:
A
differential backup is not an independent backup it always refers latest full
backup as its starting point. It backup’s all the data from Full backup to the
point of initiation of differential backup. Differential backups are faster and
smaller to create than a full database every time.
For example; Lets
consider we take a full backup F1 at 12.00 am midnight every day and
differential backup every 2 hours during the day.
Differential backup D1 starts at 2.00 am is about 1 GB.
Differential Backup D2 at 4.00 am is about 1.5 GB. Differential backup D2
contains all the data from Full backup i.e from 12.00 am to 4.00 am including
the data from differential backup D1.
D2 = D1+ Data from 2 am
to 4 am.
D2= 1GB + 0.5 GB= 1.5 GB.
Should a point of failure happens at 4.05 am by using backups F1+D2
we can restore until 4.00 am. At restore time, the full backup is
restored first, followed by the most recent differential backup.
.
Incremental backup
An
Incremental backup refers latest incremental backup if available or full backup
as its starting point. It backup’s all the data from last incremental backup if
available. If there is no incremental backup it refers FULL backup as its
starting point. Incremental backups are faster and smaller compared to
Differential and full backup.
For example; Lets consider we take a full backup F1 at 12.00 am
midnight every day and Incremental backup every 2 hours during the day.
Incremental backup I1 starts at 2.00 am is about 1 GB. Incremental
Backup I2 at 4.00 am is about 0.5 GB. Incremental backup I2 contains only the
data after I1 backup unlike differential backups i.e data from 2.00 am to 4.00
am only.
Should a point of failure happens at 4.05 am by using backups F1+I1+12
we can restore until 4.00 am.
All Incremental backups need to be in order and not corrupted
for successful restore. At restore time,
the full backup is restored first, followed by the all Instrumental’s backup in
order.
Transaction Log Backups (Full and Bulk-Logged Recovery Models Only)
The
transaction log is a serial record of all the transactions that have been
performed against the database since the transaction log was last backed up.
With transaction log backups, you can recover the database to a specific point
in time (for example, prior to entering unwanted data), or to the point of
failure.
The
transaction log backups are only valuable under the full recovery model or
bulk-logged recovery model. Each log backup covers the part of the transaction
log that was active when the backup was created, and it includes all log
records that were not backed up in a previous log backup. An uninterrupted
sequence of log backups contains the complete log chain of the database, which
is said to be unbroken. Under the full recovery model, and sometimes under the
bulk-logged recovery model, an unbroken log chain lets you to restore the
database to any point in time.
Just
like differential backup, transaction log backup is also based on full backup.
Therefore, before you can create the first log backup, you must create a full
backup, such as a database backup. Because it requires less disk space than
full backup, you can create them more frequently than database backups.
For Example: You take 1 full backup everyday at 12 am and
differential for every 2 hours starting from 2.00 am and log backup’s every 5
minutes. If a point of failure happens
at 4.04 am. You need F1+D2+ Transaction log after D2 to restore till 4.04 am.
No comments:
Post a Comment