April 3, 2007 at 4:47 am
Hi.
I thought i understood the process of backing up and restoring, but today i found i did not understand something.
What is the difference between a log file (ldf) and a transaction log file (trn)?
If you have no transaction log, can you still recover your data if you have a ldf file?
April 3, 2007 at 5:41 am
Hi Winston,
I think you're confusing transaction log files with transaction log backup files. Typically log backup files are given the extension .trn - although there is no hard and fast rule for this and you can give it another extension of you wish. Likewise, log files are typically given the extension of .ldf - but again this isn't set in stone.
So now that we've got that out of the way I guess what you're wanting to know is the difference between transaction log files (.ldf) and transaction log backup files (.trn).
I won't go into any detail regarding the transaction log as Books Online (BOL) will do a better job than me but as a start I'd suggest you read the topics, Transaction Log Fundamentals, Transaction Log Architecture and Transaction Log Physical Architecture in BOL.
In short, the transaction log is where all database modifications are recorded and the transaction log physically consists of one or more transaction log files (.ldf).
Transaction Log Backup files (.trn) are files that consist of backups that were taken of the transaction log (i.e. they are backups of all recorded transactions that are contained in the log file). Note that when a transaction log backup is taken, all commited transactions in the log file are removed. So effectively, your log backup consists of all recorded transactions since the previous log backup (in other words it's like an incremental backup of your database).
So, let's say that you take a full database backup of your database at midnight. And you took a log backup at 00:15, and at 00:16 your database blows up. Assuming that the log file (.ldf) is completely unrecoverable, your only option is to restore the full backup and subsequently restore your log backup (from 00:15), which contains all recorded modifications since that took place between 00:00 and 00:15.
If you lost the log backup file (.trn) but you still have an ldf file, intact and assuming the log was not being truncated either manually or otherwise (see "trunc. log on chkpt." in BOL) then it is possible to recover data. However, you should always take suitable log backups if you are concerned about data recoverability.
I've simplified a lot of things so I suggest you look up these topics in BOL but hopefully I've given you reasonable starting point.
Karl
April 3, 2007 at 6:27 am
that was exactly what i was looking for. thanks a million. Il do further research now, its just that my question was so simple that i think bol just didnt think to answer the question.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply