August 22, 2008 at 12:31 pm
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?
August 22, 2008 at 1:03 pm
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.
😎
August 22, 2008 at 1:10 pm
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.
August 22, 2008 at 1:21 pm
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.
😎
August 22, 2008 at 1:38 pm
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)
August 22, 2008 at 2:02 pm
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?
😎
August 22, 2008 at 2:13 pm
(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.
August 22, 2008 at 2:16 pm
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.
😎
August 22, 2008 at 2:41 pm
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
August 22, 2008 at 2:47 pm
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.
August 22, 2008 at 3:37 pm
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.
😎
August 22, 2008 at 3:44 pm
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.
August 22, 2008 at 3:51 pm
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.
😎
August 22, 2008 at 3:55 pm
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.
August 22, 2008 at 4:02 pm
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