SQL Server Transaction Backups vs Logs 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:


Weekly full backups (.bak) and hourly transaction log (.trn) backups. We keep several sets of these backups and they are regularly shipped offsite.
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:


Is it possible to restore the database from the .ldf file? What is it even for?
Is it needlessly redundant to have both of these transaction logs?
Is it important to back up the .ldf file?
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.