In a recent thread I noted that a tail log backup is essentially a regular log backup, but made with the intention of restoring the database because something is wrong with your data file. Gail Shaw (blog | @SQLIntheWild) pointed out that that’s not quite true. There are two parameters that you need to add to the BACKUP LOG command. Thanks to Gail for the correction, and here’s a little more data.
There are three options you have with a tail log backup are:
- WITH NORECOVERY
- WITH CONTINUE_AFTER_ERROR
- WITH NO_TRUNCATE
I covered the third one in doing some practice backing up of the tail log. The second one is noted in Books Online as one that you should use in the event that the database is offline and inaccessible. That will allow you to recover the last log backup (hopefully).
The first one is recommended as the one you use when the database is going to be restored and you want the end of the log.
As I mentioned in my previous post, this is a core DBA skill. It is what will allow you to recover a database with zero data loss.
Filed under: Blog Tagged: Backup/Recovery, sql server, syndicated, T-SQL 
