While going through the database forums on MSDN site, I came across a very good question about tail log backups.
As per the article : – http://msdn.microsoft.com/en-us/library/ms179314.aspx, we can take tail log backups even if the database is in offline mode. Even when my database is corrupt and not able to come online.
The question was why the tail log backup is failing when the database is offline (Essence of the discussion in the forum)
Even I tried myself and found it very interesting. Yes, it was failing for me with the 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.
Did little bit of research and thought of simulating the database crash and check if it makes any difference. Here is what I did:
1. Created a database Test
2. Took a full database backup to make sure we are able to take the t-log backup
3. Set the database to offline mode
4. Rename the mdf file of the database
5. Bring the database online which fails and the database goes to offline mode.
6. ran a query select * from sys.databases command and checked the state_desc column for 2 databases 1. was which was set to offline and 2. was the one after running the above mentioned steps.
7. Took the tail log backup for the second database and it succeeded.
Now we got to know whatever mentioned in the article http://msdn.microsoft.com/en-us/library/ms179314.aspxis correct.
Now, you may be thinking why the tail backup succeeded for the second database.
The answer is unless the database is marked as recovery_pending the tail log backup can’t be done. In the first case the database was marked as offline because there was no crash but was a normal offline. But in the second scenario, It was a crash and database wasn’t recovered due to missing mdf file.