Interactions Between Database and Transaction Log Dumps

You should not back up a transaction log unless at least one previous backup of the database has been made. The following illustration shows the interaction between backups of a database and backups of its transaction log.

Based on this illustration, if a media failure occurs at 5:01 p.m. on Tuesday, all you would need to do (assuming that the media failure was not due to a hardware problem) is load dump 5, which is a database dump that was made at 5:00 p.m. The only work lost would be whatever had been done in the minute between the database backup and the media failure.

If the media failure occurred at 4 p.m. on Tuesday, you would need to load dump 1, the database dump that was made at 5 p.m. on Friday. Then you would need to load dumps 2, 3, and 4 (the transaction log dumps made since the last complete database backup).

After all the transaction log dumps are loaded, the system is in the exact state it was in on Tuesday at 10 a.m., resulting in the loss of Tuesday's work, showing the need for frequent transaction log dumps.

In this example, the DUMP TRANSACTION statement and the WITH NO_TRUNCATE clause could have been used. In this case, successfully using the WITH NO_TRUNCATE clause is based on the assumption that the transaction log is on a separate physical drive from the database affected by the media failure. Recovery from a media failure that occurred at 4:00 p.m. on Tuesday could be done that would avoid loss of Tuesday's work.

To do this, you would load the database dump that was made at 5 p.m. on Friday and then load dumps 2, 3, 4, and the last transaction log dump made with the WITH NO_TRUNCATE clause.