SQL Server backups, in itself, is a vast subject; so vast, there are multiple books written about them. In this article, however, we are going to focus on the types of backups that are available to us, and understand how to pick what we need, and what aspects we base that decision on. This understanding would, in turn, help us decide our backup-and-restore strategy.

Following are the most common types of backups available in SQL Server:

1. Full
2. Differential
3. Transaction log
4. Tail Log backup

There are other backup types available as well:

1. Copy-only backup
2. File backups
3. Partial backups.

Full backups

A full backup, as the name implies, backs up everything. It is the foundation of any kind of backup. This is a complete copy, which stores all the objects of the database: Tables, procedures, functions, views, indexes etc. Having a full backup, you will be able to easily restore a database in exactly the same form as it was at the time of the backup.

A full backup creates a complete backup of the database as well as part of the transaction log so the database can be recovered. This allows for the simplest form of database restoration since all of the contents are contained in one single backup.

A full backup must be done at least once before any of the other types of backups can be run—this is the foundation for every other kind of backup.

continue reading Backup Types

Happy Learning!