Database maintenance and Management (II.)

Backing up and recovering databases

Full backup: A full backup includes all the data in a particular database (or a specific set of filegroups or files) and sufficient logs to recover that data;

Differential backup: A differential backup is the most recent full backup based on data. This is referred to as the variance benchmark or the differential benchmark. A differential base is a full backup of read/write data. A differential backup includes only data that has changed since the differential base was established . Typically, a differential backup that is performed for a short period of time after a baseline backup is smaller than the baseline for a full backup and is created more quickly. Therefore, using differential backups can speed up frequent backups , reducing the risk of data loss.

File backup: You can back up and restore files in the database separately. using a file backup enables you to restore only the lost files without restoring the rest of the database, which speeds up the recovery.

Recovery mode

The recovery model is designed to control transaction log maintenance and consists of 3 recovery models, the simple recovery model, the full recovery model, and the bulk-logged recovery model. Typically, a database uses either a full recovery or a simple recovery model.

Simple Recovery : Allows the database to be restored to the latest backup

Simple recovery is used only for test and development databases or for most of the data contained in a read-only database, with minimal management required for simple recovery, only to restore to the most recent full or differential backup, not to back up the transaction log, and to use minimal transaction log space.

Simple recovery is easier to manage, but if data files are corrupted, the risk factor for data loss is high.

Full recovery: allows the database to be restored to the point of failure state

Full recovery provides maximum flexibility, enabling the database to revert to an earlier point in time, preventing a maximum range of

Database maintenance and Management (II.)