SQL Server Performance

Not clear about log shipping and regular backup

Discussion in 'SQL Server Log Shipping' started by ozamora, Aug 11, 2004.

  1. ozamora New Member

    I have read about the implementation of log shipping and restoring procedure in the hot standby database, but I still have no clear idea on how sql server can maintain the two tasks (log shipping and regular log backup) separetely.

    My current production server does a FULL backup of all databases (around 20) every 2 days, and transaction log backups every 4 hours. These backups are stored in the same backup destination per database (20 backup files). INIT command is used when creating the FULL backup, NOINIT for subsequent LOG backups. LOG backups truncate the LOG after every backup.

    Now here comes my confusion. How does SQL server will now where to start the LOG backup previous to ship it to the hot standby database if there was already a regular LOG backup which have truncated the log previously?

    So in sequence

    FULL BACKUP INTO DEVICE1

    LOG BACKUP FOR LOG SHIPPING INTO DEVICE2
    LOG BACKUP FOR LOG SHIPPING INTO DEVICE2
    LOG BACKUP FOR LOG SHIPPING INTO DEVICE2

    LOG BACKUP FOR REGULAR BACKUP INTO DEVICE1 WITH TRUNCATE (IMPLICIT)

    LOG BACKUP FOR LOG SHIPPING INTO DEVICE2 (*)
    LOG BACKUP FOR LOG SHIPPING INTO DEVICE2
    LOG BACKUP FOR LOG SHIPPING INTO DEVICE2

    LOG BACKUP FOR REGULAR BACKUP INTO DEVICE1 WITH TRUNCATE (IMPLICIT)

    ... and so on

    (*) So where does the LOG backup for shipping start if there was a previous TRUNCATE.

    If I specify NO_TRUNCATE on the regular LOG backups, my logs will will grow forever right? But if that is the case, how does SQL Server maintain the correct SCNs for the regular LOG Backup and the LOG BACKUP for shipping?

    Thanks in advance for your answers.
  2. satya Moderator


    Transaction logs are divided internally into sections called virtual log files. Virtual log files are the unit of truncation. When a transaction log is truncated, all log records before the start of the virtual log file containing the MinLSN are deleted.

    For more information about virtual log files, see Transaction Log Physical Architecture & Checkpoints and the Active Portion of the Log from Books online.

    When a log backup sequence is being maintained, schedule BACKUP LOG statements to occur at intervals that will keep the transaction log from growing past the desired size.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. feann New Member

    if db backup setting is as below

    01:00 am full back up
    .... log shipping for every 15 min
    11:45 am transaction log backup (with truncation)
    .... log shipping for every 15 min

    if db failed at 2:00 pm, what is the step to recover the db


  4. satya Moderator

    11:45 am transaction log backup (with truncation)
    When you perform LOG backup with TRUNCATION then you must opt to perform full backup, other wise you cannot proceed further on the log shipping schedule. You can recover the database until 11.30 am from the last database backup.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. feann New Member

    how to backup the transaction log w/o truncate the entries
  6. satya Moderator

    A sequence of log backups provides for a continuous chain of transaction information to support recovery forward from database, differential, or file backups.

    The active portion of the transaction log can never be truncated. The active portion of the log is the part of the log needed to recover the database at any time, so must have the log images needed to roll back all incomplete transactions. It must always be present in the database in case the server fails because it will be required to recover the database when the server is restarted. The

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page