What is the primary job of a DBA? Answer is simple Backup and Recovery. Without database no DBA can exists. Today I am going to explain about the backups in sql server. The purspose of Backup is to recover the damaged database.
Full Backup:
A fullbackup is the one which backup all the data in the database and also enough transaction log for the recovery of the database.
Recovery Model : Full,Bulk,simple
Differential/Differential base Backup:
A differential backup is the one which contain only the changes after successful Fullbackup. This backup will really improve the performance of the database. So we need to restore the latest restore the latest differential backup during the recovery process.
Recovery Model : Full,Bulk,simple
Backup Types:
Partial Backups and Differential Partial Backups
These backups are designed to provide more flexibility for backing up databases that contain some read-only filegroups under the simple recovery model. However, these backups are supported by all recovery models
Partial Backup:
A backup of all the full data in the primary filegroup, every read/write filegroup, and any optionally specified read-only files or filegroups. A partial backup of a read-only database contains only the primary filegroup.
Differential Partial Backup:
A backup that contains only the data extents that were modified since the most recent partial backup of the same set of filegroups
File Backups:
The files in the database can backup and restored individually.If the database contains contains different files, located in different disks and if one of the database file is corrupted in a disk, we can restore the database file in the particular disk.
Different types of File Backups:
Full Backup
Backup of all the data in one or more files, or filegroups.
Differential Backup
A backup of one or more files that contain data extents that were changed since the most recent full backup of each file.
Transaction Log Backups
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.
Without transaction log, we can’t recover the database to point in time recovery.
Recovery model: Full, Bulk