Every SQL Server database consists of a Master Data File (an OS file with the “.MDF” extension) to store user data. Log Files, or OS files with the “.LDF” extension, are also a necessary part of every database. Log files record changes to data from requests by SQL queries. Extra data files, or OS files with the “.NDF” extension can be added to a database.
Row data files are logically grouped into one or many File-Groups
Inside every row data file, user data is kept within boundary of 8K
data pages which are further grouped as Extents.
It is hard to imagine not backing up the production databases
periodically. This facilitates recovery from accidental data deletion or complete
database corruption. IN addition to recovery,
database backups are also necessary when we need to move a database between
different instances and need to archive unused data.
SQL Server allows four different types of backups, Full, Differential,
Transaction Log, and File/File group backups.
Full Backup
A full database backup copies the entire database from all MDF,
LDF, & NDF OS files into one OS file, typically given a “.BAK” file
extension.
When a Full Database backup operation starts, the operation will
first lock the database; effectively make the row data file/s read-only, without
making the database inaccessible for ongoing & concurrent user
transactions. It does so by placing a marker in the transaction log indicating
what transactions pre-existed and will be copied to the BAK file. After the transaction log marker has been set
the lock is released and the backup will start copying database pages into the
BAK OS file.
The backup operation copies only the pages that contain data while
unused empty pages are skipped.
There is the possibility that users will modify data in the database
while the backup is in progress. Once the data page copying process is
complete, the backup operation will again lock the database; place the end marker
in transaction log, release the lock and start copying appended changes from the
transaction log between two markers.
This “log tail end” backup means every change which is from the
initial marker to the second marker should be part of full backup. In nearly all scenarios the BAK file is
up-to-date to the moment the backup operation is complete.
Full Database Backups are more disk I/O intensive
which is why full backups take longer to complete. In between two full backups,
data is secured by backing up only the changes since the most recent fill
backup. That is possible through Differential or Transaction Backups.
Differential Backup
The first 9 pages of every data file are in the same sequence. One of the 9 is the DCM (Differential Change
Map). DCM is a special header page which contain information about
extents being modified after the last full backup operation.
Differential backups starts with scanning of DCM page/s. Each bit
in a DCM page represents a single extent in the database file. Extents with bit
value 0 mean none of its data page is changed since last full backup and are
skipped during copy process.
Once extent copying process is done, just like in a full backup
process, the differential backup process also backs up the transactional log
from most recent full backup marker to bring the database to a stable state at
the time the differential operation is complete.
Differential backup only contains data that has changed since the
most recent Full backup. So the
operation takes up less space and I/O time compared to a full backup.
Therefore, for a large database we can add a full database backup
with subsequent differential database backups to our backup plan.
Transaction Log Backup
The Transactional log contains serial records for every change
made to a database data rows. Once query data changes are committed or roll
backed, the data is considered part of inactive transactions in log file.
When a transaction log backup process starts, the process starts
copying inactive transactions records created since the most recent backup
operation of the transaction log e.g. the most recent Full, Differential, or
Transaction log backup.
All transaction data considered inactive data in the transaction
log are copied to the transaction log backup OS file. The transaction log backup operation finishes
by marking inactive data space as available and ready for reuse.
As Transaction log file is sequential so creating a transaction
log backup is far more less time and I/O intensive as compared to full or
differential backups.
Critical production databases are backed up with combination of full/differential
and transactional backups.
File\File Group Backup
Larger database that consist of multiple files and file groups can
be partially backed up, by creating File or File Group backups. It is
recommended to also take transaction log or differential file\file-group backups
at relatively close in time to File/File Group backups so recovery of a database
will be founded on a consistent state backup files.
