Transaction log backup vs. Tail-log backup

• Abel4444

  SSC Rookie

  Points: 49

  More actions

  August 11, 2011 at 3:09 am

  #1367126

  If you use Full Recovery model you can take transaction log backups. Usually DBAs schedule a FULL backup(lets say scheduled daily at 1am), a differential backup (lets say scheduled every hour) and transaction backups (lets say scheduled every 15 minutes).

  Lets assume the database fails at around 2:20 am. You will now be able to backup the database to 1:00am am using the FULL backup, apply the hourly differential backup to bring it to 2:00am, apply the transaction log backup to bring the database to 2:15am.

  But if you want to recover as much data as possible. i.e. upto 2:20 am the remaining 5 minutes worth of data will have to be recovered. A tail backup will recover the last 5 min worth of data. The syntax is same as the syntax for regular log backup but "WITH NO RECOVERY" option included.

• Steven B

  SSC Rookie

  Points: 35

  More actions

  January 4, 2012 at 8:35 pm

  #1428782

  Abel4444 (8/11/2011)

  ---

  If you use Full Recovery model you can take transaction log backups. Usually DBAs schedule a FULL backup(lets say scheduled daily at 1am), a differential backup (lets say scheduled every hour) and transaction backups (lets say scheduled every 15 minutes).

  Lets assume the database fails at around 2:20 am. You will now be able to backup the database to 1:00am am using the FULL backup, apply the hourly differential backup to bring it to 2:00am, apply the transaction log backup to bring the database to 2:15am.

  But if you want to recover as much data as possible. i.e. upto 2:20 am the remaining 5 minutes worth of data will have to be recovered. A tail backup will recover the last 5 min worth of data. The syntax is same as the syntax for regular log backup but "WITH NO RECOVERY" option included.

  Thanks for the detailed explanation. It's an old thread but definitely helpful! 🙂

• SkyBVI

  SSCoach

  Points: 15115

  More actions

  January 5, 2012 at 6:20 am

  #1428957

  Ok got it....

  If the database is online and you plan to perform a restore operation on the database, before starting the restore operation, back up the tail of the log using WITH NORECOVERY:

  BACKUP LOG database_name TO <backup_device> WITH NORECOVERY

  If the database is offline and does not start.

  Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use WITH CONTINUE_AFTER_ERROR, as follows:

  BACKUP LOG database_name TO <backup_device> WITH CONTINUE_AFTER_ERROR

  Regards,

  Skybvi

  Regards
  Sushant Kumar
  MCTS,MCP

• Suresh B.

  SSC-Insane

  Points: 22986

  More actions

  January 5, 2012 at 9:10 pm

  #1429443

  SKYBVI (1/5/2012)

  ---

  ...

  If the database is offline and does not start.

  Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use WITH CONTINUE_AFTER_ERROR, as follows:

  ...

  If the database is not accessible (that is .MDF file is damaged), take tail-log backup WITH NO_TRUNCATE option.

  WITH CONTINUE_AFTER_ERROR is optional. But WITH NO_TRUNCATE is mandatory. When the database is damaged you cannot take tail-log backup without NO_TRUNCATE.