SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How do I recover a db corrupted more than 1 full backup ago, and apply all subsequent diff...


How do I recover a db corrupted more than 1 full backup ago, and apply all subsequent diff backups???

Author
Message
mark 95074
mark 95074
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40478 Visits: 38567
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).

Cool
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)
mark 95074
mark 95074
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41251 Visits: 32666
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89543 Visits: 45284
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, MVP, M.Sc (Comp Sci)
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


mark 95074
mark 95074
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search