August 11, 2011 at 3:09 am
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.
January 4, 2012 at 8:35 pm
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! 🙂
January 5, 2012 at 6:20 am
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
January 5, 2012 at 9:10 pm
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.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply