January 8, 2009 at 3:01 pm
I know there is, but I can't remember how...How do you go about restoring a database that has a corrupt/bad transaction log?
January 8, 2009 at 3:08 pm
Can you describe your scenario in more detail please? Are you restoring a backup, trying to attach a database, something else?
Do you have a clean, uncorrupted backup of this 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
January 9, 2009 at 1:05 pm
When I try to restore the database I get a message that an error was encountered processing the log file. The message says to either restore from a backup or rebuild the log. The backup I'm using is the only one I have.
The recovery model is simply, so I can't figure out what the restore is trying to do with the log. Is there a way to create a database, with simple recovery, then just restore the mdf?
January 9, 2009 at 1:22 pm
Tim Riley (1/9/2009)
When I try to restore the database I get a message that an error was encountered processing the log file. The message says to either restore from a backup or rebuild the log. The backup I'm using is the only one I have.
Can you please post the entire error message?
Can you try to do a RESTORE .. WITH CONTINUE AFTER ERROR and see if you can get any further.
Does the restore leave a suspect database, or no database at all?
The recovery model is simply, so I can't figure out what the restore is trying to do with the log.
The log is used in all recovery models to ensure database consistency after a server restart or a database restore. Without it, there's no way to ensure that the database is transactionally consistent when restored.
The way restore works is first to replace all of the pages on disk. Those pages may have half-completed transactions reflected in them because the backup reads different pages at different times as it works, and because the on-disk pages are not guaranteed to be transactionally consistent unless the database has been cleanly shut down.
Once that's done, SQL reads through the portion of the transaction log that's included in the full backup, ensures that transactions that were committed after the backup started are rolled forwards and ones that had started before the backup completed but had not committed are rolled back
Is there a way to create a database, with simple recovery, then just restore the mdf?
No. Even if you could, the database would quite likely be inconsistent and the data untrustworthy
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
January 9, 2009 at 2:07 pm
I'm not at the machine any longer so I can't provide the entire message. No, I didn't try 'continue after error', I will. The reason I'm looking to just recover the mdf file is that I know there were no uncommitted transactions; the backup was taken after a server re-boot and before anyone was in the db. Once my full backup is restored I have a differential that needs to go in. Is there a way to do this?
January 9, 2009 at 2:19 pm
Tim Riley (1/9/2009)
The reason I'm looking to just recover the mdf file is that I know there were no uncommitted transactions; the backup was taken after a server re-boot and before anyone was in the db.
It doesn't matter that you know there were no transactions. The transaction log still has to be processed. There may have been background tasks active that you don't know about but which still need to complete entirely, or not at all.
Can you post the error next time you are at the server in question?
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
January 10, 2009 at 8:58 pm
The error I receive is:
An error occured while processing the log file for database 'BFSRecovery'. If possible, restore from backup. If backup is not available, it might be necessary to rebuild the log.
Both the database and log file are created but the database is left in a 'Restoring' mode. I stop the SQL service, rename the .mdf and .ldf files, restart the service, delete the database entry, rename the .mdf and .ldf to what they should be, attach the database and it becomes available.
Now the problem is that when I attempt to restore the differential backup to bring the database up to date I receive the error: "The log or differential backup cannot be restored because no files are ready to rollforward." This doesn't make sense because the differential backup is 10 days newer than the associated full backup.
The original message mentions "rebuilding the log", how is that done? Is the problem with applying the differential due to the error in the original restore? If so, is there a way around it?
I also have another backup file that is apparently corrupted; the error is: "The media family on device 'BFS.bak' is incorrectly formed. SQL Server cannot process this media family. RESTORE HEADERONLY is terminating abnormally." I can guess the answer, but is there a way to get at this backup?
January 10, 2009 at 9:26 pm
It seems like you should have done the differential restore while you were still in restoring mode. Example from BOL as follows;
RESTORE DATABASE AdventureWorks
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks.bak'
WITH FILE = 6
NORECOVERY;
RESTORE DATABASE AdventureWorks
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks.bak'
WITH FILE = 9
RECOVERY;
The error that you posted is due to the fact that the database was not in norecovery mode ("Restoring") when you tried to restore the differential backup.
Try again with the above scenario and post your results.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 10, 2009 at 9:47 pm
Because of a problem with the log file the initial restore encounters an error. The initial restore leaves the database in the 'Restoring' mode. When I try to apply the differential without doing anything I get the error: "A previous restore operation was interrupted and did not complete processing on file 'SL_Data'. Either restore the backup set that was interrupted or restart the restore sequence."
January 10, 2009 at 9:59 pm
Can you post the statement you are using? Is the last piece of your restore statement "NORECOVERY"? If so, then that is why it is staying labeled as "Restoring". However, I have not had to use the "CONTINUE_AFTER_ERROR" so, my comments are based on working with good backups but I do believe the "NORECOVERY" is why it is staying in Restore mode.
Based on your previous post it sounds like the restore with CONTINUE_AFTER_ERROR worked. So, I was hoping that you could execute something like the following;
RESTORE DATABASE AdventureWorks
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks.bak'
WITH CONTINUE_AFTER_ERROR,
NORECOVERY;
RESTORE DATABASE AdventureWorks
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks.bak'
WITH
RECOVERY;
Let me know if that works. Thanks!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 10, 2009 at 10:15 pm
My script is:
RESTORE DATABASE [BFS]
FROM DISK = N'C:\BFS\BFS.bak'
WITH FILE = 30,
CONTINUE_AFTER_ERROR,
MOVE N'SL_Data' TO N'c:\BFS\BFS.mdf',
MOVE N'SL_Log' TO N'c:\BFS\BFS_log.ldf',
NORECOVERY
GO
RESTORE DATABASE [BFS]
FROM DISK = N'C:\BFS\BFS.bak'
WITH FILE = 43,
CONTINUE_AFTER_ERROR,
MOVE N'SL_Data' TO N'c:\BFS\BFS.mdf',
MOVE N'SL_Log' TO N'c:\BFS\BFS_log.ldf',
REPLACE,
RECOVERY
GO
The resulting message:
Processed 49600 pages for database 'BFS', file 'SL_Data' on file 30.
Processed 79330 pages for database 'BFS', file 'SL_Log' on file 30.
Msg 9004, Level 16, State 3, Line 1
An error occurred while processing the log for database 'BFS'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.
RESTORE DATABASE successfully processed 128930 pages in 56.181 seconds (18.799 MB/sec).
Msg 4319, Level 16, State 2, Line 1
A previous restore operation was interrupted and did not complete processing on file 'SL_Data'. Either restore the backup set that was interrupted or restart the restore sequence.
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.
The .mdf and .ldf files were created but left in 'Restoring' mode and the differential was not applied.
January 10, 2009 at 10:37 pm
Thanks Tim!
The message "A previous restore operation was interrupted and did not complete processing on file 'SL_Data'. Either restore the backup set that was interrupted or restart the restore sequence. " is something that is not out on the web that I could see too much so, hard to say what is going on with that. At this point I certainly don't have any answers, or suggestions as I believe you already stated you didn't have other backups to use.
Hopefully someone else has some suggestions.
Sorry.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 15, 2009 at 11:14 am
This is a case where you're not going to be able to get any further than restoring the first backup using CONTINUE_AFTER_ERROR. The log is damaged in such a way that it won't let you lay down any more log.
So - best bet for you is to restore the first backup with CONTINUE_AFTER_ERROR and RECOVERY. Then, depending on whether recovery succeeded or not, you will either have to run DBCC CHECKDB using the REPAIR_ALLOW_DATA_LOSS option - to fix up any corruptions from having to use CONTINUE_AFTER_ERROR, or put the database into EMERGENCY mode, and then run the same DBCC command to rebuild the transaction log and then run repair.
In my experience there's no way you're going to be able to lay down the second or subsequent backups though.
Hope this helps.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
January 15, 2009 at 12:14 pm
I finally gave up :crying: I spent more than 24 hours trying I don't know how many different tricks and they all ended the same way, I couldn't get past the original backup. Thanks for the advice everyone.
Tim
January 16, 2009 at 10:37 pm
Tim Riley (1/8/2009)
I know there is, but I can't remember how...How do you go about restoring a database that has a corrupt/bad transaction log?
Please take a tail log og current db, then restote full backup with no recovery and then restore log with recovery, if u got error then apply option continue_after_error...
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply