Unable to perform tail log backup?

Few days back I was asked a very interesting questions about tail log backups, thought of sharing with you

Question: Why the tail log backup is failing when the database is offline?

As per the msdn article: http://msdn.microsoft.com/en-us/library/ms179314.aspx , you can take tail log backups even if

               1) Database is in offline mode.
               2) Database is corrupt and not able to come online.


When I tried myself and found it very interesting. Yes, it was failing with the following error:

Msg 942, Level 14, State 3, Line 1
Database ‘xxxx’ cannot be opened because it is offline.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.


To identify the cause, I thought of simulating a database crash to see if makes any difference.

To simulate the scenario, I performed the following steps:

1.  Created a database  named : Test
2.  Took a full database backup to make sure I'm able to take the T-LOG backup
3.  Set the database to offline mode
4.  Rename the .mdf file of the database
5.  Tried to bring the database Test online but it failed (quite obvious) and the database goes to offline mode.
6.  To check the state_desc column of following databases:
       a) one which I manually took offline
       b) The Test database after running the above mentioned steps.

         I ran the following query: select * from sys.databases

7. Now when I attempted Tail log backup of Test database and it succeeded.

Now it's clear whatever mentioned in the msdn article is correct.

Now, you may be thinking why the Tail log backup succeeded for Test database.

The answer is unless the database is marked as recovery_pending the tail log backup can’t be performed. In the first case the database was marked as offline because there was no crash but it was gracefully taken offline. But in the second scenario, It was a crash and database wasn’t recovered due to missing .mdf file.