Backups protect your data against media failure.

When you create a database, the default location for the transaction log is the same device and in the same directory as the database file. This arrangement does not protect against media failure, and you should consider placing the transaction log in another location for production use.

Media failure on the database file   If your database file is unusable and your transaction log is usable, you can recover all committed changes to the database as long as you have a proper backup procedure in place. All information since the last backed up copy of the database file is held in backed up transaction logs, or in the online transaction log.

Media failure on the transaction log file   Unless you use a transaction log mirror, you cannot recover information entered between the last database checkpoint and a media failure on the transaction log. For this reason, it is recommended that you use a transaction log mirror in setups such as SQL Remote consolidated databases, where loss of the transaction log can lead to loss of key information, or the breakdown of a replication system.

How quickly you can recover from media failure depends on whether the media failure is on the database file or the transaction log file.

For comprehensive protection against media failure, you should keep the transaction log on a different device from the database file. Some computers with two or more hard drives have only one physical disk drive with several logical drives or partitions: if you want reliable protection against media failure, make sure that you have a computer with at least two physical storage devices.

Placing the transaction log on a separate device can also improve performance by eliminating the need for disk head movement between the transaction log and the main database file.

 See also