Join Martin Guidry for an in-depth discussion in this video Types of backup, part of SQL Server 2014 Backup and Recovery.
- View Offline
- [Voiceover] Microsoft SQL Server offers three primary backup types. The three types are full backups, differential backups, and transaction log backups. Now let's talk about each one of these individually. The full backup, as its name implies, is a backup of everything in the database. It backs up all of the data and all of the tables and gives us a complete backup of everything. While the full backup is running, users and applications are allowed to connect to the database, read data, and even possibly change data.
The last step in the full backup process is that the machine will find any changes that occured during the backup process and will apply those changes to the backup. So during a full backup, you do capture any changes that were made while you are running the backup. The next type is differential backup, and it backs up everything that has changed since the last full backup. So basically it copies the difference between now and the last backup.
Because it copies the difference, it is called a differential backup. In many cases this is a quicker backup. Because we're typically backing up only a small percentage of the database, it runs much quicker than a full backup. The trade-off there, it does mean we will have a more complex restore sequence. If all you're doing is full backups, then when you restore all you have to restore is one full backup. If you're doing a combination of full backups and differential backups, when you go to do a restore, you will probably have to restore one full backup and one differential backup.
And the last type is transaction log backups, which backs up every committed transaction in the log. So if a transaction is still underway and has not yet been committed, that will not be backed up, but all of the committed transactions will be backed up. Transaction log backups are sometimes just called log backups, or sometimes called T-log backup. When doing a restore, we'll always start with restoring the most recent full backup, and if that's the only type of backup you're doing, you would be finished then.
If you are also doing diferential backups, then your next step would be to restore the most recent differential backup, and if you're doing transaction log backups, that's always the last step is to restore the transaction log backups. You may in some cases need to restore more than one transaction log backup. It'll always be one full restore, one differential restore, but anywhere from zero to dozens of transaction log restores.
- Types of backups
- Media sets and backups
- Performing a basic backup using the GUI or command line
- Performing a basic restore using the GUI or command line
- Backing up and restoring logs
- Doing partial backup and restores
- Automating backups
- Using encryption and compression