Clarification on Tail log bkp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Clarification on Tail log bkp

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-05-15 : 14:15:45

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,

Kristen
Test

22859 Posts

Posted - 2010-05-15 : 15:30:18

My understanding of tail-log backup procedure is:

1a. Try ordinary BACKUP LOG if the database is ONLINE
1b(i). If planning to restore from the tail log (e.g. to point in time before accident) then use WITH NORECOVERY (which will prevent any further transactions being made after the backup completes, and will set the DB to RESTORE state)
1b(ii). Log will be truncated unless NO_TRUNCATE or COPY_ONLY is also used
if that fails:
2. If the database is OFFLINE (does not start) then NORECOVERY is optional
3a. If the database is Damaged try NO_TRUNCATE (avoid except when database is damaged; log file will need to be undamaged, natch!)
if that fails:
3b. Try CONTINUE_AFTER_ERROR instead of NO_TRUNCATE

If tail log is made from a damaged / offline database, or the data file(s) are missing, then the tail backup may include incomplete metadata (all actual transaction will be backed up). I think the only significance of this is that MSDB info may be incomplete, so perhaps the backup won't list correctly in the SSMS GUI (but at this point I personally would be using SQL Commands, not GUI, so that wouldn't bother me)

I think if the database is damaged then Truncate might fail, so NO_TRUNCATE may avoid that scenario.

BoL also says:

"The NO_TRUNCATE option of BACKUP LOG is equivalent to specifying both COPY_ONLY and CONTINUE_AFTER_ERROR

Without the NO_TRUNCATE option, the database must be in the ONLINE state. If the database is in the SUSPENDED state, you might be able to create a backup by specifying NO_TRUNCATE. But if the database is in the OFFLINE or EMERGENCY state, BACKUP is not allowed even with NO_TRUNCATE"

I think that last point is contradictory, other parts of BoL say that it is possible to make a tail-backup from an offline database

BoL also says that STANDBY can be used to backup a Tail Log and leave the database in STANDBY mode - I can't figure out when that would be needed! (Surely it wouldn't be possible to have additional Backups to RESTORE beyond that point ??)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-15 : 15:30:42

You only specify that option when the database is damaged. Please see BACKUP in BOL for more information.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-05-15 : 22:47:58

Got it!

Thank You.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources