SQL Server offers many options for creating backups. There are different types of backup available in SQL Server.Flowing are the most common type of backups are used day to day work in SQL Server.
- Full Backup.
- Differential Backup.
- Transaction Log Backup.
- Tail Log Backup.
There are other Backup types available as well.
- File group Backup.
- Mirrored Backup.
- Copy-only Backup.
- Striped Backup.
Full Backup:
- Backs up the entire database including transaction logs so database can be recovered after a full backup is restored,
- Full backup point-in-time recovery is possible because it contains .mdf and .ldf. It Offers complete protection against media failures.
Syntax: Backup database <dbname>to disk=’…….bak’.
| Backup database [master] to disk=’D:\backup\master.bak’ with compression, stats=1 |
Differential Backup:
Differential Backup capture all the data that has changed since the last full backup.It reduces time of recovery in case of database failure i.e no need to restore all log backups made after full backup.
Syntax: Backup database <dbname>to disk=’……….’with differential.
| Backup database [master] to disk=’D:\backup\master.bak’ with compression, differential, stats=1 |
Transaction Log Backup:
A Transaction log backup contains all the transaction log records generated since the log backup. Transaction log backup are only possible in the Full and Bulk-logged recovery models.
Syntax: Backup log <dbname> to disk=’…….trn’
| Backup log [master] to disk=’D:\backup\master.trn’ with compression, stats=1 |
