May 15, 2010 at 12:15 pm
Hi,
Small clarification...
In the Tail_log backup we are specifying additional WITH NO_TRUNCATE Option.
why do we need that, is there any significance for it. Why are we not truncating/removing the inactive/commited txns from the .LDF? If the database is still online why can't we take a normal LOG backup??
Thanks,
May 15, 2010 at 1:53 pm
From Books Online: (emphasis mine)
Backing up the tail of the log
Like any log backup, a tail-log backup is taken by using the BACKUP LOG statement. We recommend that you take a tail-log backup in the following situations:
If the database is online, before starting a restore sequence, back up the tail of the log using WITH NORECOVERY whenever the next action you plan to perform on the database is a restore operation:
BACKUP LOG database_name TO <backup_device> WITH NORECOVERY
Note: To avoid an error, the NORECOVERY option is necessary.
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 either WITH CONTINUE_AFTER_ERROR or WITH NO_TRUNCATE.
BACKUP LOG database_name TO <backup_device> [WITH { CONTINUE_AFTER_ERROR | NO_TRUNCATE }
Important: We recommend that you avoid using NO_TRUNCATE, except when the database is damaged.
If the database is damaged, for example, if the database does not start, a tail-log backup succeeds only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes.
So you shouldn't use no_truncate if the DB is online and available. Why are you?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 15, 2010 at 2:35 pm
Understood the difference!
Thanks for the justification.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply