Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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:

  • 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

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.