SQL Server Performance

Can I do a full backup for databases configured with Log Shipping?

Discussion in 'SQL Server 2005 Log Shipping' started by Jelly0228, Apr 20, 2010.

  1. Jelly0228 New Member

    If I do a full backup for databases configured with log shipping, since this operation will truncate logs, will it make the log shipping failure? If yes, then a daily backup for these kinds of databases is not allowed? if a daily backup is not possible, I don't think log shipping is a perfect high-avaibility solution. Is my understanding right?
  2. MohammedU New Member

    No, it will not fail..
    Log shipping fails when there is a log sequence breaks like when you chnage the recover model from FULL to SIMPLE and back to FULL....
  3. Jelly0228 New Member

    MohammedU, thank you for you reply , but , a full database backup also will truncate the log and break the log sequence, right? Okay, I will have a test...
  4. Jelly0228 New Member

    Hi MohammedU, you are right.
    I had a test , a full database backup for a database configured with log shipping doesn't impact its log shopping. So, it seems I have been holding an incorrect understanding, and in fact, a full database backup won't truncate any log.
    So the only way to reduce the size of sql server log file is to backup the log, right?
  5. satya Moderator

    ....also keep the transactions short and simple that will log the information.
  6. Jelly0228 New Member

    I am confused......
    If I have 2 full database backup files, one was created at 10st April and the other was created at 20th April, and a serial log backup files also created from the 10st April to now. If I carelessly lost the full database backup file created at 20th April, I can still restore the database based the one created at 10st to, for example ,today, right?
  7. Jelly0228 New Member

    well, just now I had a test, following is the steps:
    1. Doing a full database backup named dbbk1.bak, followed by a log backup, name of which is log1.trn; (From SSMS);
    2. Then doing the second full DB backup named dbbk2.bak, followed by a log backup , and the name is log2.trn.(From SSMS);
    3. Using following statement to do the restore based on the first full backup file:
    -----------------------
    restore database db_name from disk='d:dbbk1.bak' with norecovery; --successful
    restore log db_name from disk='d:log1.trn' with norecovery; --successful
    restore log db_name from disk='d:log2.trn' with recovery.--failed and error 4305 occured.
    -----------------------

    error 4305 Message text:
    The log in this backup set begins at LSN 16090000000025000001, which is too late to apply to the database. An earlier log backup that includes LSN 16090000000016500001 can be restored.
    It seems a full database backup can break the log sequence.
    Why? What does Log shipping do to make the log backup not been broken by a full database backup ?

  8. Jelly0228 New Member

    I guess Log Shipping may use "NO_TRUNCATE" or "COPY_ONLY" option to backup the log and so to keep the log sequence not been broken, right? If yes, how should I maintain the log file size since it may grow large; if I truncate the log, the Log Shipping must fail.
    I really want to know , in the production enviroment, how do you make Log Shipping efficient and keep the database log file in a reasonable size?
  9. satya Moderator

    The error is self explanatory where it shows the LSN mismatch and you cannot restore that copy of backup.
    In the production enviornment you must size the transaction log files by going thru the growth since last 3 months onwards, think about DB maintenance plan execution which contributes the size increase. See relevant blog posts http://sqlserver-qa.net/blogs/tools/archive/tags/transaction log/default.aspx here, once you have setup LS it will continue and it needs monitoring as well to ensure the log is shipped & restored on standby server.
  10. Jelly0228 New Member

    Thank Satya. I can't understanding what you said "size the transaction log files going thru the growth since last 3 months onwards", sorry my english is so poor.. sorry sorry....
    Maybe now I need change my question to: Does Log shipping truncate the transaction log? If yes, how does it keep the log sequence? if no, how should I manage the transaction log not to become large?
    Someone on the Internet said the way to prevent transaction log (of which database configured with Log Shipping) growing too large is to backup it frequently, for example, every 15 minutes.
    If yes, do you know that how does Log Shipping backup transcation log to keep the log sequence and truncate the log?

Share This Page