Sometimes I find that customers are unsure about the different types of database backups available for SQL Server. Here is a quick summary of the different types of backups available with Red Gate SQL Backup and you can use one or more type of backup to provide a complete database backup and recovery solution.
Full
A complete image of the entire database can be obtained by using a full backup.
Advantages
Doesn't require any other backup for a complete restore.
Works in any recovery model. |
Disadvantages
Backs up all the data and so is usually slower than the other backup types.
Each backup requires more disk space than other backup types. | |
Differential
Each differential backups contains the changes to the database since the most recent full backup. Just to clarify, this means that the backup is cummalitive and so you only ever need to restore the last successfull differential backup.
Since a differential backup only contains changes this means you must restore a full backup before restoring a differential.
Advantages
Good choice for large databases which aren't modified very often.
Backups often take less disk space and network bandwidth making them suitable for frequent backups. |
Disadvantages
Does not provide point in time restore.
Cannot be restored on it's own.
Cannot backup master database. | |
Transaction Log
Transaction logs are used to store modifications to the database and are seperate from the database file. I won't go into detail of transaction logs but each database must have at least one transaction log and each record stored in the transaction log is given a log sequence number (LSN) which uniquely identifies each record and the transactions must be restored in order.
A transaction log backup contains all transactions since the last transaction log backup or the last full backup if no other transaction log backups have been taken.
Advantages
Smaller size than other backup types.
Often provide faster backup times.
Point in time restore. |
Disadvantages
Cannot backup master or msdb databases. | |
Filegroups or Files
A filegroup is a group of SQL Server database files and a file/filegroup backup is ideal if you don't want to backup the whole database maybe because of disk space constraints.This type of backup does not include the transaction log part of the database so you will need to restore another database type first.
Advantages
Can be used when disk space is at a premium. |
Disadvantages
Point in time recovery not an option.
Can only be restored to database backed up from.
| |