SQL Server offers three types of recovery models: simple, full and bulk-logged. These models offer varying levels of recovery for restoring your databases from your backups. Bulk-logged recovery advantages include minimal space requirements for transaction logs and the best performance for bulk operations. Watch out, though. One disadvantage is that with a bulk-logged transaction in your backup file, you cannot do a point-in-time recovery.
In this tip, I'll explain the circumstances where you'd want to opt for bulk-logged recovery in SQL Server and what steps you'll need to take to restore a database set in that model.
As a recap, here are the differences between each recovery model:
Simple
The simple recovery model allows you to recover data only to the most recent full backup or differential backup. Transaction log backups are not available because the contents of the transaction log are truncated each time a checkpoint is issued for the database.
Full
The full recovery model can use full backups, differential backups and transaction log backups to provide a safety net against complete failure. Along with being able to restore a full or differential backup, you can recover the database to the point of failure or to a specific point in time. All operations, including bulk operations such as SELECT INTO, CREATE INDEX and bulk-loading data, are fully logged and recoverable.
Bulk-logged
The bulk-logged recovery model protects against failure and offers the best performance. In order to get better performance, the following operations are minimally logged and not fully recoverable: SELECT INTO, bulk-load operations, CREATE INDEX as well as text and image operations. Under the bulk-logged recovery model, a damaged data file can result in having to redo work manually based on the operations that are not fully logged. In addition, the bulk-logged recovery model allows the database to be recovered to the end of a trans
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
action log backup only when the log backup contains bulk changes.
Benefits of bulk-logged
The primary benefit of of using the bulk-logged recovery model is that it minimizes the space requirements for the transaction log so it can track every single operation that is part of the transaction. This is helpful if you have large systems where you import large sets of data or create large indexes. The transaction still gets committed and written to your data file, but the space requirements for the transaction log are not as great as with the full recovery model. From testing, you will see that inserting data still requires a lot of transaction log space, but the space needed to create indexes is greatly reduced.
The way SQL Server backups and recovery models work, you have the option of changing your model from full recovery to bulk-logged recovery and back as needed without interfering with your transaction log backups. This allows you to keep the size of your transaction log in check when doing large bulk-logged transactions.
The one thing to note is how the transactions are written to your transaction log backup. If there is a bulk-logged transaction in your backup file, you cannot do a point-in-time recovery. This is one of the biggest things you will notice with the restore process. When a bulk-logged transaction exists in a transaction log backup, you have to restore the entire transaction log.
Backup process
The backup process works the same way, whether you have your database in the full or bulk-logged recovery model. You still have the option of using full, differential or transaction log backups. The commands are exactly the same, as shown here:
BACKUP DATABASE Test TO DISK='C:\Test_full.BAK'
BACKUP DATABASE Test TO DISK='C:\Test_diff.BAK' WITH DIFFERENTIAL
BACKUP LOG Test TO DISK='C:\Test_log.TRN'
Another key thing to note with your transaction log backups is the size of the transaction log backup file. If you set your database to bulk-logged recovery and issue a minimally logged transaction like CREATE INDEX, you will see that your transaction log itself does not get that large. But when you issue the transaction log backup statement, the backup file may be a lot larger because of all the data that needs to be replayed in order to restore your database to the same state.
Restore process
The restore process is very similar to the restore perspective . The one difference is that you cannot do a point-in-time restore if the transaction log backup file contains any bulk-logged transactions. If you try to issue the restore using point-in-time recovery, and the transaction log backup has bulk-logged activity, you will get this error message:
Server: Msg 4327, Level 16, State 1, Line 1
The log in this backup set contains minimally logged changes. Point-in-time recovery is inhibited. RESTORE will roll forward to end of logs without recovering the database.
You can use the following commands to do a restore when the database is set to bulk-logged recovery: (As you can see, these commands are identical regardless of whether the database is in full or bulk-logged recovery.)
RESTORE DATABASE Test FROM DISK='C\Test_full.BAK' WITH NORECOVERY
RESTORE DATABASE Test FROM DISK='C:\Test_diff.BAK' WITH NORECOVERY
RESTORE LOG Test FROM DISK='C:\Test_log.TRN' WITH RECOVERY
What real benefits do you gain from using bulk-logged recovery? A lot depends on your environment. If you are issuing a huge amount of bulk insert statements and dropping and recreating indexes on the fly for batch processing, the bulk-logged recovery model may be the way to go. If you have a normal environment, where the data is not constantly in flux, the full recovery model may be the better bet.
With any option you choose, the simpler route often causes more headaches further down the line, so choose your recovery model wisely and plan your recovery process accordingly.
[TABLE]
