Post reply

Tail Log backup.

  • December 11, 2009 at 9:24 am

    #207350

    Hello Experts

    I need a quick response from you guys.

    Suppose check the below scenario to understand my requirement:

    In my project the Full database backup is being performed everyday night at 10 P.M. There is no Differential backup and transaction log backup is taken in every 15 mins interval. Now my database crashed at 10:10 PM. How to retrieve the 10 mins data?

    [ans:] I know the answer for this. It is we need to get the 'tail backup' so that as much as data we can retrieve from the active transaction log buffer. But how to do it? Since my database is crashed and I'm unable use the database to fire the below query which is used for 'tail backup'.

    ======== Example Syntax =================

    USE master;

    --Create tail-log backup.

    BACKUP LOG AdventureWorks

    TO DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'

    WITH NORECOVERY;

    Please suggest!!

    Thanks.

  • December 11, 2009 at 9:53 am

    #1091270

    Why are you doing the log backup to what looks like a file with full DB backups in? That's just confusing.

    Try this

    BACKUP LOG AdventureWorks

    TO DISK = 'Z:\SQLServerBackups\AdventureWorksTailLog.trn'

    WITH NO_TRUNCATE;

    From Books Online:

    NO_TRUNCATE

    Specifies that the log not be truncated and causes the Database Engine to attempt the backup regardless of the state of the database. Consequently, a backup taken with NO_TRUNCATE might have incomplete metadata. This option allows backing up the log in situations where the database is damaged.

    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 online.

    If the database is in the OFFLINE or EMERGENCY state, BACKUP is not allowed even with NO_TRUNCATE.

    If that doesn't work, then nothing will and the last portion of the log is lost, along with any transactions that occurred in it. That's why the frequency of log backups should be determined by the maximum allowable data loss for the application.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 11, 2009 at 9:59 am

    #1091276

    As you have quoted from "Books Online"::

    "If the database is in the OFFLINE or EMERGENCY state, BACKUP is not allowed even with NO_TRUNCATE.

    "

    (1) For the above case, what is the potential solution to retrive solution to minimise data loss?

    (2) If the database crashes\damaged what is the way to retrieve the data? Does tail backup help in this scenario..?

    Regards

    Sourav

    Thanks.

  • December 11, 2009 at 10:11 am

    #1091293

    Sourav-657741 (12/11/2009)

    (1) For the above case, what is the potential solution to retrive solution to minimise data loss?

    As I said

    If that doesn't work, then nothing will and the last portion of the log is lost, along with any transactions that occurred in it. That's why the frequency of log backups should be determined by the maximum allowable data loss for the application.

    That covers the situations where the DB is offline or in emergency mode.

    (2) If the database crashes\damaged what is the way to retrieve the data? Does tail backup help in this scenario..?

    Try taking a tail-log backup and

    If that doesn't work, then nothing will and the last portion of the log is lost, along with any transactions that occurred in it. That's why the frequency of log backups should be determined by the maximum allowable data loss for the application.

    Once you have the tail log backup, you can then restore full backup, all log backups in sequence ending with the tail log backup and be restored right up to point of failure. If you couldn't take the tail log, then it's you lose that last portion of transactions.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 11, 2009 at 10:16 am

    #1091295

    Thanks for the response.

    Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply