locked
Insufficient Log Data RRS feed

  • Question

  • The mirror database, "UOP_PIMB", has insufficient transaction log data to preserve the log backup chain of the principal database.  This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)

     

    I've taken a backup while the database is online and applied it to my "other" server.  What do I need to do to have both in sync to get mirroring to work.  My way around this is to restore the backup to my primary.

     

    thanks.

    Friday, April 14, 2006 3:41 PM

Answers

  • Correct. Usually, you must restore a log backup after restoring the full backup to the server that will be the initial mirror.

    You don't _always_ have to restore a log backup. For example, create a new database, take a full backup and restore it to the partner server. You can start mirroring without doing a log backup/restore.

    BOL has been changed so that it now states that you should restore a log backup to the mirror before starting mirroring.

    If you really want to know _why_ you sometimes need to restore a log backup, grab me at a conference and we'll talk for 15 minutes or so. It was quite the interesting bug that we found around this.

    Thanks,

    Mark

    Monday, April 17, 2006 5:20 PM
  • Tech Ed this June in Boston.

    Also, anything SQL related in Seattle. PASS usually.  Looks like they are going to be here (Seattle) in November.

    Then there is this:

    http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032290575&EventCategory=4&culture=en-US&CountryCode=US

    Thanks,

    Mark

    Monday, April 17, 2006 11:38 PM

All replies

  • Even though the BOL don't mention this, I have always had to apply a full backup AND a transactional backup to the mirror server. A full backup did not suffice. Can anyone confirm this?
    • Proposed as answer by Cwilson2012 Wednesday, January 22, 2014 4:34 AM
    Friday, April 14, 2006 4:43 PM
  • Correct. Usually, you must restore a log backup after restoring the full backup to the server that will be the initial mirror.

    You don't _always_ have to restore a log backup. For example, create a new database, take a full backup and restore it to the partner server. You can start mirroring without doing a log backup/restore.

    BOL has been changed so that it now states that you should restore a log backup to the mirror before starting mirroring.

    If you really want to know _why_ you sometimes need to restore a log backup, grab me at a conference and we'll talk for 15 minutes or so. It was quite the interesting bug that we found around this.

    Thanks,

    Mark

    Monday, April 17, 2006 5:20 PM
  • What conferences are in the near future for sql server 2005 that you'll be at (North America)?

    thanks.

    Monday, April 17, 2006 6:03 PM
  • Tech Ed this June in Boston.

    Also, anything SQL related in Seattle. PASS usually.  Looks like they are going to be here (Seattle) in November.

    Then there is this:

    http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032290575&EventCategory=4&culture=en-US&CountryCode=US

    Thanks,

    Mark

    Monday, April 17, 2006 11:38 PM
  • Hallo,

     

    I had the same problem so i restored the Datanase and the transactionlog but I now received another errormessage:

    "The remote copy of database "mirror_Test" has not been rolled forward to a point in time that is encompassed in the local copy"

    this issue however was resolved after i backed up and restored the Database and the Transactionlog 3 times in a row .

     

    this was only a small database in our testLab but is there a explaination / solution for this problem so i dont have to backup and restore our 60 Gb production db 3 times ( or more ) in a row ?

    thanks in advance & Kind regards

     

    Maarten

     

    Tuesday, April 24, 2007 12:14 PM
  • Hi, Mark,

    We are trying to use COPY_ONLY option to backup the FULL and LOG, and use that to create the mirror db. We got same error. Is this a known limitation of COPY_ONLY option? (we want to use COPY_ONLY option so that it won't affect our regular backups)

    Thanks!

    George
    Wednesday, July 11, 2007 5:03 PM
  • 1. I presume the log must be restored in "No Recovery" mode. Is that correct?

    2. I just realized this discussion was for sql 2005. Does that same "restore a transaction log" issue apply to sql 2008?


    TIA,

    Barkingdog
    • Edited by edm2 Thursday, September 17, 2009 8:46 PM edit
    Thursday, September 17, 2009 8:44 PM
  • I just setup Mirroringon a sql 2008 box and also had to restore a translog!

    I thought that was a bug -- why isn't it fixed in sql 2008?

    TIA,

    barkingdog
    • Proposed as answer by MikLemme Wednesday, December 16, 2009 8:59 PM
    Sunday, September 20, 2009 11:30 PM
  • Hi, Mark,

    We are trying to use COPY_ONLY option to backup the FULL and LOG, and use that to create the mirror db. We got same error. Is this a known limitation of COPY_ONLY option? (we want to use COPY_ONLY option so that it won't affect our regular backups)

    Thanks!

    George

    Same thing here. Figured out that you can't use the COPY_ONLY option when using the tran log to set up mirroring. Makes sense in a way since copy only doesnt update the last LSN number... So I'm guess that though the contents of the tran log backup are the same with or without the COPY_ONLY option the last LSN to be backed up has to match from the principle to the mirror.
    Wednesday, October 21, 2009 4:08 AM
  • The workaround for this is:
    1) Disable your transaction log backup job
    2) set your primary database to Simple recovery mode save the change
    3) set recovery mode back to Full again
    4) backup the database
    5) restore on the mirror server in the "RESTORE WITH NORECOVERY" state
    6) setup mirroring
    7)  re-enable your transaction logging on the primary server
    • Proposed as answer by GOTWE Richard Tuesday, August 31, 2010 2:34 PM
    Wednesday, December 16, 2009 9:06 PM
  • Mike,

    The above steps worked for me.

    Wednesday, December 22, 2010 3:35 PM
  • Thanks MikLemme, been stuck on this for hours.  Works perfectly for me!
    Tuesday, October 25, 2011 9:06 PM
  • --> Restore Full backup from the principal to mirror then

    --> Take Only Transaction Log backup of the principal database

    --> Copy it to Mirror and restore the log 

    --> Run the mirror Config security Wizard 


    • Edited by Yogi.n Thursday, November 24, 2011 2:47 PM
    Thursday, November 24, 2011 2:27 PM
  • Thanks. it helps me a lot. I think we need log backup because of

    1- Restart database engine will update log status on both databases.

    2- Difference in backups on both sides.



    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    • Proposed as answer by Rohan Odendaal Wednesday, February 27, 2013 5:16 AM
    • Unproposed as answer by Rohan Odendaal Wednesday, February 27, 2013 5:16 AM
    Tuesday, April 24, 2012 12:06 PM
  • What you could try is setting the db recovery to simple, shrink the the log file, then set the recovery mode back to full. From there you can backup the db and move the backup file to the mirror server and restore with the NORECOVERY option. When the db is restored on the mirror (restoring state) get onto the principal server and setup mirroring again. Worked for me.
    Wednesday, February 27, 2013 5:22 AM