SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Two Types of Tail Log Backups

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:


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

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


No comments.

Leave a Comment

Please register or log in to leave a comment.