This post may not be the most technical or advanced dicussion but I think it’s important that everyone knows what is available within SQL Server and the differences between them.
Full Backup – This is like it sounds a full copy of the databases. Full backups take the most time to complete but they are also the easiest form of backup to recover from. A full backup is required before you can do any other type of backup. If you run full backups once daily in the case of failure your potential data loss is ~ 24 hours (depending on time of failure).
Differential Backup – This is a backup of everything that has happened in your database since the last full backup. Differential backups are smaller and faster then a full backup but in order to recovery from a differential backup you need to restore the full backup first and then the differential backup. If you run differential backups every 6 hours your potential data loss is ~ 6 hours (depending on time of failure).
Transaction Log Backup – This is a backup of everything that has occured since the last full or differential backup or since the last transaction log backup. Transaction log backups are usually very small and very fast to complete but the recovery time required to restore from a transaction log backup is longer then the other backup types. In case of failure you would need to restore your full backup, any differential backups (if applicable), and then every transaction log taken since the last full (or differential backup). If you run transaction log backups every 15 minutes your potential data loss is ~ 15 minutes (depending on time of failure).
