12 April 2013
SQL Server has a number of backup strategies revolving around Full backups, Differential Backups and Transaction Log backups.
These do exactly what they say … take a full backup of the database.
A differential backup includes only the data that has changed since the Full backup.
Transaction Log backups
These backup the transaction logs which are stored independently of the data.
Depending on data volumes, a full backup may be run daily, weekly or monthly. Any database restore will be based on the last full backup, so the more frequent the better, providing it does not impact on performance and is within storage limits. If only full backups are used in a Simple recovery model, then data loss in the event of failure is everything since the last Full backup.
Using Differential backups allows for smaller intermediate backups. A simple backup strategy using