jonsuns7
asked on
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,
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,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Paul Jackson
http://churmura.com/techno
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/
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/
ASKER
Great Thanks for the help