What is Backup & why do we need it: The backup is nothing but snapshot of a database. The purpose of the backup is to create a copy of data that can be recovered in the event of a primary data failure
Before we discuss on SQL Server backups, let’s try to understand what backups are all about. Backup is a copy of SQL Server database, that can be used to restore and recover database in case of a failure.
Types of Backups:
Below are the different types of backups available in SQL Server.
- Full backup
- Differential
- Transaction log
- Tail Log
- Copy-only
- File and File group
- Partial backups
Full Backup:
Full backup, as name states it is a copy or snapshot of whole database, which contains all objects i.e tables, views, stored procedures, functions etc. Full backup also contains transaction logs so that the database can be recovered in one go.
A full backup must be done at least once before taking any other SQL Server backups as it is the foundation for all types.
Differential Backup:
Differential backup will only record the data that has changed since the last successful full backup. Internally, when you run a differential this option will backup all extents that are changed since last full. Differential backups are useful especially when subset of data is modified most often than the rest of the database.
Transaction Log Backup:
A transaction log backup stores a series of logs that provide history of every modification of data in the database. The log backup can be taken only when the database is set to full recovery model. T-log option is used to recover a database to point-in-time. This means that the transaction log backups are incremental and differential backups are cumulative in nature. If a database to be restored to a specific point, you need to restore last full, recent different backup and corresponding transaction log backups to successfully recover the database.
Tail-log Backup:
A tail log backup captures any log records which has not been backed up by the Transaction log backup. The Tail-log backup works only if you have taken full backup and your database must be in Full or Bulk-logged recovery model.
Copy-Only Backup:
A copy-only backup is a special type of backup in SQL Server, this is independent of the sequence of conventional SQL Server backups. Copy-only backup is useful to take a backup for a special purpose without affecting the existing backup sequence (LSN). Restoring copy-only backup is same as restoring any other full backup
File and Filegroup Backup:
A file backup allows you to take copy of each file independently when you have multiple files in your Database.
A filegroup backup allows you to take copy of all files that are in a particular filegroup. By default, each database has a PRIMARY filegroup that is tied to at least one file that is created.
Partial Backup:
The partial backup, which was introduced in SQL Server 2005. The partial backup allows you to exclude read-only filegroups which means when you take partial backup, it contains the data in PRIMARY file group, every read-write file group and optionally one or more read-only filegroup or files.
Summary:
Planning backups simple and easy for smaller database. As database grows, the management of SQL Server backup is a tedious task. It is good to define best backup strategy, that would place ourselves into in a good position in any sort of failure.
More Info on Backups:
