December 2, 2015 at 1:18 pm
I am working on getting a large databse into an availability group...
The database is 7tb in size...and takes 2 days to back up. about a month ago we were able to run a full backup and also restored that to another server in NORECOVERY.
Due to the size and design complexities of the system (which I could list but don't feel like beating a dead horse)...we also need to take a FULL backup of the primary filegroup only...along with partial differentials and log backups
I was under the impression that I would be able to do the following and be successful...and I had come up with a test case to prove it out but at the moment it doesn't seem to be the case.
Edit: I will also note this database has Filestream enabled and that comprises about 99.95% of the space consumed...in addition, this database is replicated from a source system...and past conceptions let parties to believe backing up this database was redundant in nature...those feelings have changed after being shown a worst case scenario...thus we are trying to make this destination system highly available.
#1. Take a full backup of this Large DB in simple recovery mode
#2. Once finished, flip to full recovery mode and run a differential to reset the LSN
#3. Start taking log backups
#4. Resume the primary filegroup backup job (weekly)
#5. Run partial differential backups (daily)
#6. Continue on with tlog backups.
Then at some point in the future when we were ready to get the AG server up and running I would be able to take a regular differential of the database (which I did) and restore and complete the process...
Today was the day we were trying to take that regular differential and apply that differential (which was about 3 weeks post FULL).
I accidentally, pointed the restore to use the partial differential backup files...and when I ran the restore command I received the following:
Msg 3178, Level 16, State 1, Line 1
File </db name/>is not in the correct state to have this differential backup applied to it.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I realized my error and then changed the files to the correct regular differential...and expected all to be well...
I got the unfortunate error...
Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Crud!
I went online to look for direction on evaluating my backup files to make sure the LSN numbers are correct and I am not missing something obvious.
Upon review I restored the header for the full backup files as well as my regular differential.
I used the following msdn blog as reference and from what I can tell these files should work.
My FULL Checkpoint_LSN is: 6700275000000660100118
My DIFF database_backup_lsn is: 6700275000000660100118
At this point the only thing I can think of is by me accidentally using the partial differential files I screwed up the database that was in NORECOVERY mode.
Sorry if this sounds totally ignorant, can someone offer any advice on what to look for or where I may have tripped up on this whole process?
Thanks,
Lee
December 2, 2015 at 5:14 pm
Since I can't run another full backup at this moment in time, I am simply re-restoring the previous FULL backup I have which will take about 2 days to restore with NORECOVERY...and I am hoping I can simply run the additional differential restore...
I have looked it over several times and I cannot see why it wouldn't have restored the first time...
I have triple checked the "RESTORE headeronly" on the full and the diff and again it shows the full backup having the following CheckpointLSN value of 6700275000000660100118 and running the same "RESTORE headeronly" on the differential backup the DatabaseBackupLSN value is also 6700275000000660100118.
If anyone can recommend a different way that review / verify I have an intact LSN chain I am all ears...
Thanks in advance,
Lee
December 3, 2015 at 1:49 am
Are you including your filegroup restore as part of your recovery process?
I suspect the fielgroup backup is invalidating previous differentials LSNs that were done.
December 3, 2015 at 8:06 am
Justin Manning SA (12/3/2015)
Are you including your filegroup restore as part of your recovery process?I suspect the fielgroup backup is invalidating previous differentials LSNs that were done.
I am not, if I understand your question correctly.
I am simply restoring the complete FULL backup I took about 3 weeks ago over a weekend...in norecovery
Since that time, there have been multiple "COPY_ONLY" backups of the primary filegroup as well as regular primary filegroup backups taken along with primary differential backups and t-logs.
Yesterday I ran a manual normal differential backup of the database and then tried to restore that to the database I had in the NORECOVERY state.
I hope that clarifies your question, if it does not please let me know.
Lee
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply