I’m currently studying for the 70-450 exam to obtain my MCITP in SQL Server Database Administration. I wanted to get a better understanding for SQL Server’s transaction log. I wanted to see if I could corrupt a databases MDF and recover data even after the last t-log backup. This is where the tail backup comes in.
The tail log backup can recover your data even if the MDF is corrupted. This is because it stores all the transactions since the last full backup and t-log backup. If the MDF is corrupted your t-log should still be able to be backed up, unless that is corrupted too. If the database is not truly corrupt, a backup log statement with the norecovery option will work just fine. This puts your database into recovery mode and prevents users from going into the database, preserving the state of the database so the restoration can be performed.
Unfortunately this does not work when the database is corrupted. You have to use the option continue_after_error, this forces the backup to occur.
You can then restore all data back to the database using your full and t-log backups.
Immediately after bringing the database online, be sure to make a full backup. This way you have a good restore point and if you routinely are backing up your t-logs, your next transactional backup will work just fine.
Refactoring Self 