May 5, 2010 at 12:35 pm
We just found out one of our db's has created corrupt backups for a month.
Is it possible to take that last valid backup and restore it to another server and then use the transaction logs (that fortunately have not been flushed) to restore the missing data for the month gap?
May 5, 2010 at 12:44 pm
If you have all the transaction log backup files since the full backup you want to use for the restore, yes.
May 5, 2010 at 12:46 pm
Of course, after I post my answer, I have to come back and ask you for clarification as to what you are trying to accomplish.
May 5, 2010 at 1:27 pm
We are trying to restore current data to a backup that is a month old using the latest ldf file.
May 5, 2010 at 1:29 pm
mckinnj1 (5/5/2010)
We are trying to restore current data to a backup that is a month old using the latest ldf file.
So you are saying you don't have the t-log backups, just an older full backup. Sorry, you can't use the ldf file the way your are trying to us it.
May 5, 2010 at 1:44 pm
To do that you need either all the log backups since that full backup (not a single one can be missing), or the database mush be in full recovery, must never have been switched to simple recovery any time since that full backup and must never have had an explicit log truncation.
Are either of those the case?
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
May 5, 2010 at 2:04 pm
I don't believe so.
When we restore the 1 month old BAK file with the ldf file, we get I/O errors when accessing some tables. I am guessing this is due to a possible gap in time between the ldf and BAK files.
I tried running the DBCC CHECKDB command and it failed as well citing the I/O errors.
Any ideas?
May 5, 2010 at 2:16 pm
mckinnj1 (5/5/2010)
I don't believe so.When we restore the 1 month old BAK file with the ldf file, we get I/O errors when accessing some tables. I am guessing this is due to a possible gap in time between the ldf and BAK files.
I tried running the DBCC CHECKDB command and it failed as well citing the I/O errors.
Any ideas?
It is possible that the database was already corrupt when your backup was made.
You should first try restoring the DB only from the old backup file and then run DBCC CHECKDB to see if it was already corrupt when the backup was made.
May 5, 2010 at 2:20 pm
Also, it sounds like you are trying to use the mdf from the older backup of the database and the ldf of a newer copy of the database; not going to work.
The transaction log file (ldf) is not meant to be used that way.
May 5, 2010 at 2:22 pm
mckinnj1 (5/5/2010)
I don't believe so.
Who's that in reply to? If it's in reply to my post, please feel free to explain why I'm wrong.
When we restore the 1 month old BAK file with the ldf file, we get I/O errors when accessing some tables. I am guessing this is due to a possible gap in time between the ldf and BAK files.
How, exactly, did you do that? Step by step please.
A 'gap in time' between ldf and backup is not going to cause corruption. SQL point-blank will not restore log backups if the LSNs don't match. Attempted tricks like switching the ldf created by the backup with the current one will result in SQL marking the database suspect (maybe recovery pending) because the files don't match.
I tried running the DBCC CHECKDB command and it failed as well citing the I/O errors.
Any ideas?
Means either the backup you used was corrupt or that the IO subsystem corrupted the database after you restored it.
Post the full and complete output of CheckDB.
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
May 5, 2010 at 2:45 pm
Michael Valentine Jones (5/5/2010)
mckinnj1 (5/5/2010)
I don't believe so.When we restore the 1 month old BAK file with the ldf file, we get I/O errors when accessing some tables. I am guessing this is due to a possible gap in time between the ldf and BAK files.
I tried running the DBCC CHECKDB command and it failed as well citing the I/O errors.
Any ideas?
It is possible that the database was already corrupt when your backup was made.
You should first try restoring the DB only from the old backup file and then run DBCC CHECKDB to see if it was already corrupt when the backup was made.
I just ran the DBCC against a new db from the BAK by itself and the BAK file is corrupt.
We have a third party vendor that will have to repair the BAK file.
Thanks for all the help!!
May 5, 2010 at 2:53 pm
Didn't know you could run DBCC CHECK DB against a .bak file.
Were you able to restore the database itself as under a differnet name using the .bak file?
May 5, 2010 at 3:48 pm
Lynn Pettis (5/5/2010)
Didn't know you could run DBCC CHECK DB against a .bak file.
You can't. CheckDB can only be run against an actual database, not a backup. I belive Paul R worked on something to allow CheckDB to run against a backup, but it never made it into the product. Will ask him for details.
It's certainly not an option in 2000/2005/2008. CheckDB requires a parameter of database name or ID or, if left out, will check current database.
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
May 5, 2010 at 5:29 pm
Maybe you mean you validated the backup and it failed, or you ran DBCC against the restored database and that failed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2010 at 5:41 pm
Could you show us the restore command you're using, the messages you get out of the restore operation, and the DBCC commands you issue after the restore is complete? Some of what you've posted doesn't make logical sense, as others have pointed out, so it's not clear what you've attempted or why it failed.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply