A Microsoft SQL database consists of at least two files, a data file (.mdf) and a transaction log file (.ldf). The data file contains all the data for the database, as you would expect. The transaction log file contains a record of every transaction against the database, since the last transaction log backup. All changes in the database are first saved to the transaction log, before being updated in the main data file.
The transaction log file can allow a database administrator to recover a database back to a specific point in time, rather than simply back to the last full backup. If you find there was some sort of database corruption at 4:30pm, you could recover the database back to 4:29pm using the transaction log file.
It is generally good practice to store the data and log files on different physical drives, or different raid arrays, providing both speed advantages, and protection from hard drive crashes. Keep in mind not to use RAID 0. While RAID0 provides some speed advantages, it provides no redundancy in the event of a drive failure.
The transaction log file can be backed up independently of the main database backup if required.
If you want to be able to take full advantage of transaction logging, you will need set the recovery model option for the database to “Full”. You could also have the recovery model set to “Bulk-Logged”, however any bulk operations will not be logged. If the database is set to “Simple”, you can only recovery from a previous full backup.
For production environments, it is recommented to use the “Full” recovery model.
