Differential not restoring

  • Restored most recent full backup with NORECOVERY.

    Attempt to restore differential backup and get "backup cannot be restored because the database has not been restored to the correct earlier state".

    Restore HEADERONLY results:

    Full backup

    First LSN: 117267000012837600152

    Last LSN: 117281000015665300001

    Checkpoint LSN: 117267000012837600152

    DatabaseBackupLSN: 115768000002956500193

    Differential backup

    First LSN: 115767000017493200003

    Last LSN: 115884000022034300001

    Checkpoint LSN: 115768000002956500193

    DatabaseBackupLSN: 112308000022358800108

    Now, I look at msdb..backupset and see that the differential backup I am trying to restore is indeed marked as type 'I'. However, unlike the other differential bacups the differential_base_lsn is NULL.

    I'm assuming that means something went wrong with the differential backup and I just need to suck it up and start over. Correct?

  • Pam Brisjar (8/22/2008)


    Restored most recent full backup with NORECOVERY.

    Attempt to restore differential backup and get "backup cannot be restored because the database has not been restored to the correct earlier state".

    Restore HEADERONLY results:

    Full backup

    First LSN: 117267000012837600152

    Last LSN: 117281000015665300001

    Checkpoint LSN: 117267000012837600152

    DatabaseBackupLSN: 115768000002956500193

    Differential backup

    First LSN: 115767000017493200003

    Last LSN: 115884000022034300001

    Checkpoint LSN: 115768000002956500193

    DatabaseBackupLSN: 112308000022358800108

    Now, I look at msdb..backupset and see that the differential backup I am trying to restore is indeed marked as type 'I'. However, unlike the other differential bacups the differential_base_lsn is NULL.

    I'm assuming that means something went wrong with the differential backup and I just need to suck it up and start over. Correct?

    I did some checking on one of my systems here in the msdb.dbo.backupset systm table, and from what I see there and looking at the information you have provided above, it doesn't appear that this differential backup was created after this particular full backup. In msdb.dbo.backupset what I noticed was that checkpoint_lsn for the last full backup matched the database_backup_lsn for all subsequent transaction and differential backups. Looking at the info above, it appears that the differential backup you are trying to restore is actually older than the full backup you are trying to apply it to.

    Edit:

    The database_backup_lsn of the transaction and differential backups also match the first_lsn of the full backup to which they may be applied.

    😎

  • Thanks for checking, Lynn. Something definitely went awry. There is no way that the differential backup happened before the full backup, though. backupset confirms the dates.

    Here's a thought, though. I did a filegroup backup in-between the two. (Don't ask - you really don't want to know.) Am wondering if this was the monkey wrench.

  • From BOL:

    A differential file backup requires a full file backup as a base. For more information about full file backups, see Full File Backups.

    Yes, the file group backup may have messed things up. Can't answer that from experience, as I haven't ever done a file group backup and then needed to restore from it.

    😎

  • I didn't restore from the filegroup backup. That's for something completely different (developer's version of partitioning/archiving that I have to support).

    Backup sequence:

    FULL backup (type "D" in backup set)

    Filegroup backup (type "F" in backupset)

    Differential backup (type "I" in backupset)

    Restore sequence:

    Full backup with NORECOVERY

    Differential backup (fails)

  • Out of curiosity, what are the values for the first_lsn, last_lsn, checkpoint_lsn, database_backup_lsn, and last_recovery_fork_guid for these three backups from msdb.dbo.backupset?

    😎

  • (first_lsn, last_lsn, checkpoint_lsn, database_backup_lsn, last_recovery_fork_guid)

    Differential:

    117267000012837600152

    117281000015665300001

    117267000012837600152

    115768000002956500193

    E6DE692C-CC38-4630-895E-07D6FF628D47

    Filegroup:

    115911000022357800058

    115926000014383500001

    115912000000983700174

    115768000002956500193

    E6DE692C-CC38-4630-895E-07D6FF628D47

    Full:

    115767000017493200003

    115884000022034300001

    115768000002956500193

    112308000022358800108

    E6DE692C-CC38-4630-895E-07D6FF628D47

    Hmmm, so maybe I do have to do the filegroup restore. That would suck.

  • If it works like most other backup chains, yes, it looks like you need to restore the full backup. the file group backup, then the differential backup.

    😎

  • If you want to be able to ignore the file group backup in your backup chain - look at running that backup with COPY_ONLY.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • That's a great thought, Jeffrey. If SQL Server is treating the filegroup backup as part of the backup chain (which it appears that it is) that would make sense. Somehow, though, having the filegroup backup as part of the chain just seems wrong to me.

    Meh. I'm running the restore with the filegroup backup now. Once that's done I'll try the differential backup and see what happens. This will take several hours so I'll report back when I'm done.

  • Be careful with the COPY_ONLY option. You may not get what you are expecting. I am assuming that the file group backup is being taken for DR purposes.

    😎

  • You would be assuming incorrectly.

    The filegroup backup is for "archiving".

    The whole (very ugly) story:

    Client has an olap database that grows at a rate of ~40 GB/day (currently)

    Hardware resources limit the total size of the database to ~2.8 TB

    They have not implemented partitioning and have no interest in doing so

    Solution is to manually "peel off" the oldest set of data at regular intervals

    To accomplish this, a new filegroup and file are created at the defined interval

    The oldest filegroup is backed up (just in case someone wants the data at a later time) and the data is deleted from the "live" database.

  • Then I would carefully test the use of the COPY_ONLY. One of two things will happen. One, it won't work. Two, you may not be able to restore it. Like I said earlier, I have not had the opportunity to work with file group backups and restores.

    You may also want to consider a full backup after the file group backup.

    😎

  • Oh yeah, I'll definitely be doing some testing of all this. Of course testing on a 3 TB database on seriously underpowered machines is a very, very painful thing. As a full backup takes about 8 hours to run and (at least currently) requires deleting the other full backup first, I'd prefer to avoid that route.

  • If you can, I'd test on a much smaller database. If you have access to SQL Server 2005 Developers Edition ($50 USD), you could install it on your desktop, create a small database (or use AdventureWorks) for testing. That way you aren't hitting the production server, and you can test to your hearts content with a MUCH smaller database.

    😎

Viewing 15 posts - 1 through 15 (of 17 total)

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