Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do I recover a db corrupted more than 1 full backup ago, and apply all subsequent diff backups??? Expand / Collapse
Author
Message
Posted Friday, April 4, 2014 5:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 7, 2014 9:17 PM
Points: 3, Visits: 15
'morning all

I've had a report of missing data (restore from full was done, diff wasn't applied) in a db that has the following backup schedule:
daily full @10pm
6 hourly diffs (12/6/12/6)
15min log

I now have to restore the db as at 4 days ago, then restore all data added/changed since then.

In testing, have found i can no longer restore the last diff from day 2, ie:
restored full from day 1 (norecovery), restored last diff from before next full backup (norecovery), and now trying to restore last diff from day2 (taken after 2nd full), and getting the well documented error:
"This differential backup cannot be restored because the database has not been restored to the correct earlier state"

I understand the reason for the error, but what is the procedure for recovering in this situation? must I restore each subsequent full db as well? (overwrite/no overwrite, norecovery?)

thanks in advance
Post #1558685
Posted Friday, April 4, 2014 7:42 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:34 PM
Points: 23,343, Visits: 32,079
mark 95074 (4/4/2014)
'morning all

I've had a report of missing data (restore from full was done, diff wasn't applied) in a db that has the following backup schedule:
daily full @10pm
6 hourly diffs (12/6/12/6)
15min log

I now have to restore the db as at 4 days ago, then restore all data added/changed since then.

In testing, have found i can no longer restore the last diff from day 2, ie:
restored full from day 1 (norecovery), restored last diff from before next full backup (norecovery), and now trying to restore last diff from day2 (taken after 2nd full), and getting the well documented error:
"This differential backup cannot be restored because the database has not been restored to the correct earlier state"

I understand the reason for the error, but what is the procedure for recovering in this situation? must I restore each subsequent full db as well? (overwrite/no overwrite, norecovery?)

thanks in advance


Differential backups are tied to the full back taken prior to them. After restoring with norecovery the the full backup from day 1 and the last differential taken before the next full backup (norecovery) you then have to start restoring all the transaction log backups taken after that differential backup (with norecovery on all except the last transaction log backup).



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1558689
Posted Saturday, April 5, 2014 12:46 AM This worked for the OP Answer marked as solution
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 7, 2014 9:17 PM
Points: 3, Visits: 15
Lynn Pettis (4/4/2014)


Differential backups are tied to the full back taken prior to them. After restoring with norecovery the the full backup from day 1 and the last differential taken before the next full backup (norecovery) you then have to start restoring all the transaction log backups taken after that differential backup (with norecovery on all except the last transaction log backup).


thanks for the quick reply Lynn,

had thought to try the umpteen log file restore, however the sql error when trying a trans log restore of the first logfile created after last diff day1 (so; full, diff, log1) is:

"...
System.Data.SqlClient.SqlError: The log in this backup set terminates at LSN 2804703000000039200001, which is too early to apply to the database. A more recent log backup that includes LSN 2804992000000193600001 can be restored
..."

yet none of the more recent logfiles work either.
Post #1558698
Posted Monday, April 7, 2014 6:15 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:23 PM
Points: 15,664, Visits: 28,062
Are you sure the log backups and the full/differential are from the same database on the same server? There has to be a log that overlaps with the LSN on the differential if you have all the logs and they're from the same source.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1559023
Posted Monday, April 7, 2014 7:34 AM This worked for the OP Answer marked as solution


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 42,849, Visits: 35,978
mark 95074 (4/5/2014)
"...
System.Data.SqlClient.SqlError: The log in this backup set terminates at LSN 2804703000000039200001, which is too early to apply to the database. A more recent log backup that includes LSN 2804992000000193600001 can be restored
..."

yet none of the more recent logfiles work either.


Sounds like either you are missing a log file or the log chain was broken somehow. Check the MSDB backup histories for the backups which exist, you should be able to see if you're missing any files, also check the error log make sure there are no entries about the recovery model being changed.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1559070
Posted Monday, April 7, 2014 9:20 PM This worked for the OP Answer marked as solution
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 7, 2014 9:17 PM
Points: 3, Visits: 15
Thanks to all.

turned out to be an impossibility in the end; logs created post restore belonged to 'new' db, no longer applicable to original backup. missing data recovered using db diff tool.
Post #1559338
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse