12

I've inherited a moderately-sized business-critical SQL Server 2008 database and am trying to wrap my head around backup planning. (I'm a developer, not a DBA.)

The way our system is set up right now there are two backup systems:

  1. Weekly full backups (.bak) and hourly transaction log (.trn) backups. We keep several sets of these backups and they are regularly shipped offsite.
  2. SQL Server logs (.ldf), with recovery model set to Full. This file is on a separate drive from the main .mdf file, but is otherwise not backed up.

In the event of an emergency restore (or when restoring backups to a dev machine), my procedure is to use the .bak files and then apply the .trn files. We have a script that makes this procedure relatively straightforward.

My questions:

  1. Is it possible to restore the database from the .ldf file? What is it even for?
  2. Is it needlessly redundant to have both of these transaction logs?
  3. Is it important to back up the .ldf file?

1 Answer 1

17

No, it it's not possible to restore a database from an ldf file. The ldf file would be restored along with the mdf files.

No, it's not redundant as they have two different purposes.

It's important to take full backups, and transaction log backups. Only having a copy of the ldf file doesn't help you restore the database.

As to what a ldf file is for, the ldf is the transaction log. Think of it as a circular buffer that records changes to your database. When you update a row, the change is immediately written to the ldf. At some point in the future (usually less than five minutes), the modified data is written to the mdf file.

If the server crashed or there was a power failure, when SQL starts, it reads the ldf and re-applies (REDO) those changes.

Additionally, if you have a transaction that hasn't been commited and the sever crashes, all changes made by that transaction have to be undone to make the database consistent. The ldf file has that task as well. (UNDO)

I mentioned above that the ldf file is circular. Taking a transaction log backup (.trn) copies out a portion of the ldf file. After a trn file is safely created, sql can reuse that portion of the ldf file. The series of trn backups create a chain that together record every modification made to the database. Of course, if you never took a tranaction log backup, the ldf file would grow and grow and grow.

In a disaster scenario, restoring the full backup gets you a copy of the database as of the time the full backup finished. You can then restore the trn files in order and bring the database current to any point in time including up to the last trn backup.

I'm glossing over some important details, but the gist is the that ldf is a working file that records recent changes to the database. The trn files are copies of parts of the ldf made under the assumption that you will keep then safe so that sql can reuse the space in the ldf and if disaster strikes, you'll have them in an alternate location.

3
  • 2
    +1 Very nice answer. The only thing I would add is that when a DBA says "Database" they mean the .mdf (the data file) and .ldf (the log file) files combined. The two files together make a single unit. On some databases you may even see multiple .mdfs and/or .ndfs (secondary data files) as well. These files are also combined together to make the single unit called the database. If you lose any of them you are in a disaster mode and have to take corrective action. Apr 18, 2013 at 3:48
  • +1 good answer, also don't mistake backing up the 'physical file' (the actual .mdf/.ndf/.ldfs) while live on a system, even with a 3rd party app like Norton while MS SQL Server is running as a safe 'backup'. Those files are highly sensitive, thus actual MS SQL Server backup files should always be created when possible. Apr 18, 2013 at 3:51
  • Thanks, this is very helpful. It sounds like our regular SQL Server backup plans are enough, and there is no need to tamper with the .mdf or .ldf files directly.
    – Hank
    Apr 18, 2013 at 3:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.