none
Restore a point between two full backups RRS feed

  • Question

  • Hi,

     

    I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:

     

    1. Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past.
    2.  Restore the server with Oct 1 full backup with NORECOVERY option.
    3. Try to restore to the point at Oct 5 12:00, with the transaction log.

     

    But the restore fails and  SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?

     

    Thanks

    Friday, October 23, 2015 11:41 AM

Answers

  • Below is the FirstLSN & LastLSN for Oct 1 full backup, Oct 10 full backup, and the tranaction log (Oct 23) backup:

    6944000010146600196    6944000010165000001

    6972000009816600266    6972000009831800001

    6976000038827900001    6976000076749600001

    Note the gap between the last LSN in the first FULL backup and the first LSN of the second FULL backup provided by your customer.  This suggests transaction log backups were done between the FULL backups.  The log records needed for point in time recovery are not available without those log backups.  An unbroken sequence of LSNs is required for point in time recovery.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, October 24, 2015 1:00 PM
  • The Oct 01 Full Backup ends at LSN 6944

    The Oct 10 Full backup ends at LSN 6972

    The Log backup starts at LSN 6976.

    That indicates the log file has been truncated sometime after Oct 10.  This may be because there were Log Backups taken which you are missing.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Saturday, October 24, 2015 1:03 PM

All replies

  • Hello,

    A log backup bases always on the latest full backup, in your case the Oct 10 backup; so you can not do a Point-in-time restore to a date before Oct 10 using the log backup.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 23, 2015 11:56 AM
  • It sounds like your FULL backup may have been taken using the COPY_ONLY parameter. If so then your full backup would not be able to serve as a base for your transaction log and you would get that error.

    You can look at the backup metadata of those files by using the HEADERONLY directive.

    RESTORE HEADERONLY FROM DISK = 'C:\myfullbackup.bak'
    RESTORE HEADERONLY FROM DISK = 'C:\mylogbackup.trn'
    GO

    Look at the First and LastLSN columns to tell.


    Regards,
    Mark Broadbent.
    Microsoft Certified Master
    Contact me through twitter | blog | sqlcloud


    Please click "Propose as answer" if a post solves your problem
    or/and vote the post up if it's been helpful.

    Friday, October 23, 2015 12:01 PM
  • He took (and is using) a FULL backup on the 1st Oct as his base - which unless he used copy_only for that (as I mention in my reply) should be fine to restore the transaction log chain taken after.

    Regards,
    Mark Broadbent.
    Microsoft Certified Master
    Contact me through twitter | blog | sqlcloud


    Please click "Propose as answer" if a post solves your problem
    or/and vote the post up if it's been helpful.

    Friday, October 23, 2015 1:18 PM
  • If you need to bring your server to a point in time in your case(Oct 5), only thing you can do is

    • Restore Oct1 backup which is the latest u have with respect to your Target date- Oct 5

    But the restore fails and  SQL Server said the transaction log does not contain the point. The point is too early. Why?

    You cannot bring it to Oct 5 as you mentioned you have your first transaction log backup on Oct 23.

    Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?

    .LDF file contains all of your transaction which is committed and uncommitted. When you shrink the ldf file, all inactive portions get shrinked and space het released to disk. So you cannot consider these inactive portions and all to be carried in the backup and hence the differnce


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Friday, October 23, 2015 3:05 PM
  • Right.  The _first_ Full backup must not be COPY_ONLY; for subsequent FULL backups it doesn't matter.  But if both Full backups were COPY_ONLY then the BACKUP LOG would fail.

    Other options include a missing log file, or that the database was in SIMPLE recovery on or at some point after Oct 1.

    Note, these statements are false:

    "A log backup bases always on the latest full backup"

    A Full Backup never breaks the log chain ("The Fleetwood Mac rule")

    "You cannot bring it to Oct 5 as you mentioned you have your first transaction log backup on Oct 23"

    The transaction log backup will contain all transactions back to the last log backup. Or the first non-copy-only Full backup after the database was set to FULL (or BULK) recovery.

    David


    David http://blogs.msdn.com/b/dbrowne/




    Friday, October 23, 2015 5:12 PM
  • Hi,

     

    Let me explain the case in more details.

     

    The database comes from a customer. The customer sent the full backups on Oct 1(Oct_1.bak) and Oct 10(Oct_10.bak) and want to restore to the point at Oct 5. Both full backups are NOT copy-only backups. And he also sent the CustomerDB_Oct_23.MDF and CustomerDB_Oct_23.LDF on Oct 23.

     

    After getting his files, I try to do as follows:

     

    1. Start SQL Server 2008 on my local computer.
    2. Attach the CustomerDB_Oct_23.MDF & CustomerDB_Oct_23.LDF files to the SQL Server. The LDF file is 13GB.
    3. Invoke “BACKUP LOG CustomerDB TO DISK ‘CustomerDB_Log.bkf’ WITH FORMAT” to backup the transaction log of the database. The CustomerDB_Log.bkf is only 200MB. I can confirm the database on both customer server and my server is in “Full Recovery Mode” and have not switched to “Simple Recovery Mode” on customer server during the database lifetime.
    4. Restore the database to a new database CustomerDB_Restore with Oct_1.bak and NORECOVERY option.
    5. Try to restore to the point at Oct 5 12:00, with the transaction log CustomerDB_Log.bkf generated in step 3. But the restore fails and  SQL Server said the transaction log does not contain the point. The point is too early.

     

    It seems the problem comes from step 3 but I cannot figure out why the backup of the log does not contain the point on Oct 5.

    • Edited by chcw Saturday, October 24, 2015 4:11 AM
    Saturday, October 24, 2015 4:07 AM
  • There are only a few possibilities here. If your database has always been in the full recovery model, then from the moment of the first full database backup, all the log entries would have been kept in the .LDF waiting to be backed up. If the log file has never been backed up, then the first log backup would be able to be restored onto any database restore. So something must've happened. Perhaps there's a log backup you're unaware of. Perhaps the database was put into the simple recovery model for a while. ...ok, that's pretty much it. The portion of the log that you're looking for is either somewhere (because it was backed up) or it's nowhere (because it was put into the simple recovery model). I'd suggest you do some more research into the backup history on the customer server.

    Blog: http://sqlblog.com/blogs/rob_farley
    Company: http://www.lobsterpot.com.au
    Community: Adelaide SQL UG President

    Did this help? Mark it as an answer!

    Saturday, October 24, 2015 6:08 AM
  • Ok, that helps.

    What does

    RESTORE HEADERONLY FROM DISK = 'C:\myfullbackup.bak'
    RESTORE HEADERONLY FROM DISK = 'C:\mylogbackup.trn'

    Show about the FirstLSN and LastLSN for the full and log backups?

    Can you RESTORE without STOPAT?  If so you're in business; you just need to find the right LSN to STOPAT.  If not, you're missing a log backup.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Saturday, October 24, 2015 11:07 AM
  • Hi, David,

    Below is the FirstLSN & LastLSN for Oct 1 full backup, Oct 10 full backup, and the tranaction log (Oct 23) backup:

    6944000010146600196    6944000010165000001

    6972000009816600266    6972000009831800001

    6976000038827900001    6976000076749600001

    I use the SQL Server Management Studio to do the restore. And if use a normal restore, I can restore to Oct 1 or Oct 10 without any problems.

    Thanks

    Saturday, October 24, 2015 11:55 AM
  • Below is the FirstLSN & LastLSN for Oct 1 full backup, Oct 10 full backup, and the tranaction log (Oct 23) backup:

    6944000010146600196    6944000010165000001

    6972000009816600266    6972000009831800001

    6976000038827900001    6976000076749600001

    Note the gap between the last LSN in the first FULL backup and the first LSN of the second FULL backup provided by your customer.  This suggests transaction log backups were done between the FULL backups.  The log records needed for point in time recovery are not available without those log backups.  An unbroken sequence of LSNs is required for point in time recovery.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, October 24, 2015 1:00 PM
  • The Oct 01 Full Backup ends at LSN 6944

    The Oct 10 Full backup ends at LSN 6972

    The Log backup starts at LSN 6976.

    That indicates the log file has been truncated sometime after Oct 10.  This may be because there were Log Backups taken which you are missing.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Saturday, October 24, 2015 1:03 PM