Q: What is a full SQL Server database backup and explain the internal process performed?
A full backup is a complete point in time
version of the data and code. It allows the DBA to restore back to that point
in time for recovery. Full backups are needed prior to both differential and
transaction log backups.
SQL Server follows the below steps during a BACKUP
operation:
- A full database backup forces a database checkpoint
to flush all data to disk.
- The backup process reads the data pages and
writes them to the backup file.
- Once the data reading operation is complete, it
reads the Transaction Log.
- The
full database backup does not contain all of the records from the transaction
log. Instead, it chooses only those records that are necessary to make the
restored copy of the database transaction consistent. Those records begin
either from the latest active transaction or from the checkpoint created
by the backup.
- The process writes to the system tables in the
MSDB database including the Backup LSN and backup history.
Q: What is a SQL Server differential backup?
Answer: SQL Server differential database
backups contain only extents that have changed since the last full
backup. The changes are denoted by flipping a bit at the extent level to
determine if the extent needs to be backed up or not
Q: How do SQL Server differential backups work internally?
Answer: SQL Server stores data in 8 KB
pages and an extent is comprised of eight pages. Each Global Allocation Map
interval (GAM interval) covers 64,000 extents and has a special database page
called a differential character map (DCM). The DCM tracks which extent has
changed since the last full backup. A SQL Server differential database backup
reads all the bitmaps and backs up the data extents that are marked as changed
for the differential backup. These bits are reset once a full database
backup is executed.
Q: For differential backups, how is the data
determined for those backups?
DCM page contains information about the extent
which are changed after the Full backup. Diff. backup process reads information
about the changed extents and those extents are added in the differential
backup.
Q: How many copies are allowed when taking a
backup using MIRROR Backup option?
Three copies are allowed in a Mirror
backup apart from the original copy.
Q: What is Recovery Time Objective (RTO)?
Recovery Time Objective (RTO) is the amount of
time which data or hardware is desired to be restored after a data corruption
or hardware failure.
Q: What is Recovery Point Objective (RPO)?
Recovery Point Objective (RPO) describes a
point in time that data can be restored from. For instance, if there is data
corruption, Data loss or unavailability, at what point in time can a valid copy
of the data be restored from? RPO is usually measured as minutes, hours, days,
weeks etc.
Q: What is Tail log backup?
A tail-log backup captures any log records
that have not yet been backed up (the tail of the log) to prevent work loss and
to keep the log chain intact. Before you can recover a SQL Server database to
its latest point in time, you must back up the tail of its transaction log. The
tail-log backup will be the last backup of interest in the recovery plan for
the database.
Q: Tail log backup is taken in below ways:
--If the database is online
follow below syntax:
BACKUP LOG [database name] TO [backup device] WITH NORECOVERY
--If the database is
offline (example a corrupted database which does not start]
BACKUP LOG [database name] TO [backup device] WITH CONTINUE_AFTER_ERROR
Please visit other related articles...
https://sqldbaiq.blogspot.com/p/backuprestore-part-2.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-3.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-4.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-5.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-6.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-7.html
References: Thanks to the all
the SQL Server bloggers who wrote and shared the valuable information on their
blogs which helped me a lot to prepare this series of Questions. Also big
thanks to Microsoft Documentation which contains each and everything about
their product.
No comments:
Post a Comment