Tail log backup... What is it and how to use it?

If you attempt to restore an existing database (with out REPLACE switch) which is in full or bulked log recovery mode, you need to take a tail log backup before you start your restore.

Technically tail log backup is the last back of log files and then it puts the database into recovery mode. This makes impossible for anyone to change anything on the database after you take a tail log backup. Hence you have a guarantee that you have a good backup the database till the last moment it was online before you overwrite/restore it.

Tail log backup is taken in below ways:

If the database is online follow below syntax:

BACKUP LOG [database name] TO [backup device] WITH NORECOVERY

If the database is offline (example a corrupted database which does not start]

BACKUP LOG [database name] TO [backup device]  WITH CONTINUE_AFTER_ERROR

If you are keen to know more about this technology, please refer to http://msdn.microsoft.com/en-us/library/ms179314.aspx