| With the combination of full database and differential backups, it is possible to take snapshots of the data and recover them. But in some situations, it is also desirable to have backups of all events that have occured in a database, like a record of every single statement executed. With such functionality, it would be possible to recover the database to any state required. Transaction log backups provide this functionality. As its name suggests, the transaction log backup is a backup of transaction log entries and contains all transactions that have happened to the database. The main advantages of transaction log backups are as follows: Transaction log backups allow you to recover the database to a specific point in time. Because transaction log backups are backups of log entries, it is even possible to perform a backup from a transaction log if the data files are destroyed. With this backup, it is possible to recover the database up to the last transaction that took place before the failure occurred. Thus, in the event of a failure, not a single committed transaction need be lost.
As with differential backups, you need a base full database backup in your strategy to recover a database from transaction log backups. A backup strategy using transaction log backups is represented in Figure 3-2. Full database backups are done during the off-peak hours and transaction log backups are done at predefined times during the day. A transaction log backup contains all transactions that have occurred since the previous transaction log backup. Therefore, to restore a database using transaction log backups, the full database backup and all transaction log backups since the full database backup was taken are needed. As you can see, it is important to have all the backups available. If the full database backup or one of the transaction log backups is missing, it is not possible to perform the restore as desired. Figure 3-2. A backup strategy with transaction log backups. 
Combining Transaction Log and Differential Backups Another possibile backup strategy is to combine full database, differential, and transaction log backups. This is done when restoring all transaction log backups would take too much time. Because restoring from a transaction log backup means that all transactions have to be executed again, it can take a great deal of time to recover all the data, especially in large databases. Differential backups only apply data changes, which can be done faster than re-executing all transactions. To recover a database when you have a combined backup strategy, as shown in Figure 3-3, you need to restore the last full database backup, the last differential backup, and then all subsequent transaction log backups. Figure 3-3. A combined backup strategy. 
For example, to recover to transaction log backup point T3, you must restore the full database backup F1, the differential backup D1, and the transaction log backup T3. |
The time period you should allow between transaction log backups depends on: The quantity and size of the transactions occuring in the database. For this backup strategy, SQL Server has to store all transactions until they are saved by a transaction log backup. Therefore, the transaction log file must be capable of holding all transactions that happen in the time period between two consecutive transaction log backups. If the log file fills up too fast, you can decrease the time between transaction log backups or increase the size of the log file. The acceptable amount of work loss. As mentioned above, it is possible to recover up to the last transaction if the data files are lost. But if a transaction log becomes lost or damaged, it is only possible to recover up to the last transaction log backup. Decreasing the amount of time between transaction log backups will reduce the amount of work lost if this situation occurs.
The Full Recovery Model As mentioned before, you need to tell SQL Server in advance which backup strategy you plan to implement. If only full database and differential backups are used, the database has to be set to the simple recovery model. If you also want to use transaction log backups, the recovery model must be set to FULL (or BULK_LOGGED). The full recovery model tells SQL Server that you want to perform transaction log backups. To make this possible, SQL Server keeps all transactions in a transaction log until a transaction log backup occurs. When the transaction log backup happens, SQL Server truncates the transaction log after the backup is written to the backup device. In simple mode, the transaction log is truncated after every checkpoint, which means that committed transactions (which are already written to the data files) are deleted from the transaction log. Thus, in simple mode, transaction log backups cannot be created. Important  | It is important to perform transaction log backups when your database is in full recovery mode. If no transaction log backups are done, the log file will increase to its maximum size. When it is full and cannot increase anymore, it will no longer be possible to perform transactions. The code for performing transaction log backups can be found in the section titled "Performing Transaction Log Backups." |
To set the recovery model to FULL, use the ALTER DATABASE statement again. The following code sets the recovery mode of AdventureWorks database to FULL: USE master; GO ALTER DATABASE AdventureWorks SET RECOVERY FULL; GO
The Bulk-Logged Recovery Model In the full recovery model, all bulk operations (single operations that alter large amounts of data) are fully logged to make the restore of a transaction log backup possible. In some databases, this recovery model cannot be used all of the time because of limitations on transaction log size and the performance issues arising from fully logged bulk operations. Therefore, the bulk-logged recovery model exists. It allows a transaction log backup to capture both the log and the results of any bulk operations, but it does have drawbacks. Under the bulk-logged recovery model, it is not possible to restore a database to a specific point in time. It is also no longer possible to perform a transaction log backup when the data file is damaged and a bulk operation has occurred since the last transaction log backup. This was one of the main benefits of transaction log backups. Therefore, the bulk-logged recovery model should be turned on only in the time periods when bulk operations are performed and should be used for as short a time as possible. The rest of the time the full recovery model should be used. Don't use the bulk-logged recovery model if you don't have any problems using only full logged operations. For further information, see the SQL Server Books Online topic "Backup Under the Bulk-Logged Recovery Model." |
Performing Transaction Log Backups To perform a transaction log backup, your recovery model should be set to full and you must have performed at least one full database backup since changing to the full recovery model. Transaction log backups are done with the BACKUP LOG statement. As always, the name of the database and the backup device must be provided. Backup device types are the same as those used for full database and differential backups. To back up the transaction log of AdventureWorks to a physical device, perform the following steps: Backing Up the Transaction Log File 1. | Set the recovery model to FULL.
| 2. | Perform at least one full database backup.
| 3. | Backup the transaction log of AdventureWorks to a physical device using the following SQL statements:
USE master; GO BACKUP LOG AdventureWorks TO DISK='t:\adv_log.bak'
As with the other backup statements, the backup process appends to the backup device when no option is specified in the BACKUP statement. To overwrite the device, the WITH INIT statement is used.
USE master; GO BACKUP LOG AdventureWorks TO DISK='t:\adv_log.bak' WITH INIT
|
|
