Sql-server – DPM transaction log backups of SQL Server partially contained databases

Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?

Even taking a transaction log backup is not supported for databases using the SIMPLE recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.

A transaction log backup maintenance plan task automatically skips databases in SIMPLE recovery to avoid causing errors.

Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.

For the reasons I mentioned above, it won't matter for databases using SIMPLE recovery, as they will be skipped by the transaction log backup task.

For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.

Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.

Even though SIMPLE recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)

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.