Please enable JavaScript.
Coggle requires JavaScript to display documents.
Backup and restoring databases (RECOVERY MODELS (Bulk-logged…
Backup and restoring databases
A backup that is created by specifying READ_WRITE_FILEGROUPS is known as a partial backup.
RECOVERY MODELS
Simple recovery
requires the least administration
transaction log backups are truncated on a regular basis
Full recovery
Allows you to restore to a point in time
the log files record all SQL transactions and the time they were performed
disadvantages
the logs can grow a lot
you will need to shrink and truncate the logs
Bulk-logged
least used model
compromise between simple and full recovery
allows good performance while using the least log space
disadvantages
cannot do a point in time recovery
UNDERSTANDING DATABASE BACKUPS
SCOPE OF BACKUP
whole database
partial database
set of files or filegroups
for each of these, SQL server supports full, differential and incremental backups
TYPE OF BACKUP
choose the type of backup that is best for your environment
BASED ON
simplicity
time to perform a backup
time to perform a restore
TYPES
FULL backup
contains all the data in a specific database or set of filegroups or files to allow recovering that data
DIFFERENTIAL backup
based on the latest full backup of the data
known as the base of the differential, or the differential base
contains only the data that has changed since the differential base
differential backups that are taken fairly soon after the base backup are smaller and faster to create than the base of a full backup
using differential backups can speed up the process of making frequent backups to decrease the risk of data loss
a differential base is used by several successive differential backups
at restore time, the full backup is restored first, followed by the most recent differential backup
INCREMENTAL backup
based on the last backup of the data
contains only the data changed since the last full or incremental backup
smaller and faster to create than full and differential backups
at restore time, full is restored first, followed by each incremental backup following the full
TAKE NOTE
over time, the amount of data included in differential backups increases