Understanding Backup Options

[pcarlson]

There are 4 backup options in the SQL Server advanced Options section. I try to describe them including a description, when to use and questions. If people have corrections, answers or added clarity to this options I would much appreciate it.

 

1) Full Backup

Description: Backups up database + transaction log and truncates transaction log

 

When to Use: When you first backup your database and everytime to want to ensure that you don't need multiple backups to restore your data?? You may want to run this weekly or more often depending on backup strategy.

 

Questions: I think this one is pretty clear.

 

2) Differential Backup

Description: Backups all changes to the database since the last full backup.

 

When to Use: After you have completed a full backup, and just want to back up the changed data. Perhaps daily??

 

Questions: How does this work? Do it backup the transaction log or something else? How does this differ from Log no truncate?

 

3) Log Backup

Description: Backups up transaction log and truncates commited transactions. Free space in log file becomes available based on the number of commited transactions.

 

When to Use: When you want to backup the transaction log instead of the entire database. Requires a full backup to restore on top of. This also requires that you take each backups changes since the last full backup and incrementally restore them??

 

Questions: This one is also clear.

 

4) Log No Truncate

Description: Backups up transaction log, but does not truncate transactions. Free space in log file does not change.

 

When to Use: Must first have a full backup, then you can apply just the last backed up transaction log for easy restore.

 

Questions: When I use this option, the log indicates that it's backing up the whole database (111 GB), but it happens in 4 min so this is not possible with my backup system. Does this do anything else besides backup the transaction log? How does this differ from Differential backup?

 

Thanks for any clarity or corrections to my understanding of how the advanced options work for the SQL Backup.