• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

Backup Types and Options

Hi Everyone,

Bear with me since I am new to SQL Server. I have previously worked with Oracle.

I was taking some backups of different databases on my server. These include the System Databases, Master, MSDB, Model and so on.

I was wondering, why do some user databases only give me the option to do Full and Differential Backups, while others, allow me to do Full, Differential and Transaction Log backups. Can anyone explain why this is?

I thought at first it might be the Recovery Option for the paticular databases, but it turns out that some of the user databases that have their recovery option set to simple, will allow any type of backup, whereas others only allow the Full and Differential options. Can anyone explain how these options are determined.

Thank You,  
  • 4
1 Solution
Paul JacksonCommented:
A database won't give the option for a transaction log backup if its recovery mode is set to Simple.
You can check this setting by right clicking the database and selecting properties and looking at the options settings.
Paul JacksonCommented:
The reason you are getting the option to backup a database that is set to simple recovery mode is because even in simple mode the database will still use a transaction log in case a transaction rollback is required but the transaction log gets truncated when a checkpoint occurs assuming there are no uncomitted transactions at the time of the checkpoint.

A checkpoint will occur :
(from sqlbooksonline)

If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:
The log becomes 70 percent full.

The number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option.

Long-Running Transactions
The active portion of the log must include every part of all uncommitted transactions. An application that starts a transaction and does not commit it or roll it back prevents SQL Server from advancing the MinLSN. This can cause two types of problems:

If the system is shut down after the transaction has performed many uncommitted modifications, the recovery phase of the subsequent restart can take considerably longer than the amount of time specified in the recovery interval option.

The log may grow very large because the log cannot be truncated past the MinLSN. This happens even if the database is using the simple recovery model, in which the transaction log is normally truncated on each automatic checkpoint. Replication Transactions
The active portion of the log must also contain all transactions marked for replication, but that have not yet been replicated to a subscriber. If these transactions are not replicated in a timely manner, they can also prevent truncation of the log.

See here for deeper explanation : http://msdn.microsoft.com/en-us/library/ms179355.aspx
jonsuns7Author Commented:
Great Thanks for the help

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now