Restoring a corrupted database?
-
Okay, here's the long and short of what the company I work for is dealing with: there was a hard drive failure in the middle of our nightly database backups, keeping the most critical database (the one with all the really good data) from backing up fully or corrupting--we're not sure. The database contains about four months worth of revenue information for a casino, so it'd be fantastic if we could restore it, though not absolutely necessary (since they have most of the important stuff on paper).
When I try to restore the database, using either WITH RECOVERY or WITH NORECOVERY, the restoration fails at past 90%. This is the message I get:
[...]
90 percent processed.
Msg 3183, Level 16, State 2, Line 1
RESTORE detected an error on page (0:978) in database "cms" as read from the backup set.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I'm relatively sure that the database is corrupt. I looked into STOPAT and CONTINUE_AFTER_ERROR, but neither gave me any better results. Is there any way that we can restore this database up to the 90% mark where it fails?
Any help would be appreciated. I do have two coworkers who I may be able to ask about this, but this has been named my pet project (as I'm the DBA in training... LOTS of training) so I'm hoping I can mostly figure it out on my own, or with a little guidance.
-
Have a look here: http://msdn2.microsoft.com/en-us/library/ms190952.aspx and you might also want to have a look at DBCC CHECKDB once you have it going.. http://blogs.neogeo.com/blogs/marchoeppner/archive/2006/01/01/31.aspx.
One other thing to check, which I have only ever seen once but had the same symptom as your are describing, is that you are using the same MAXTRANSFERSIZE in your restore as you did in your backup - if this is not specified during the backup and you are restoring to a SQL Server with the same version then you should be fine - its a long shot but it might be worth checking.
- James
--
James Moore
Red Gate Software Ltd -
How would you go about checking the MAXTRANSFERSIZE of the backup?
We made another go with the CONTINUE_AFTER_ERROR option with no luck, and then attempted to run the EMERGENCY mode/form of ALTER, but we got an error that basically said "nope, can't touch the database while it's restoring, bub." After we try to run the restore, the database will hang at (Restoring) and won't let you do a thing to it until the database is deleted. The interesting thing is that you can actually see the size of the database change--the database file (in the Data folder) goes from 2,064 KB to 264,000 KB. So it looks like (and excuse me if I'm wrong, I am still really new to this) that some of the data IS restoring, but because we get caught at 90% and stuck in the restore mode, we can't access anything whatsoever.
If anyone has any other suggestions, I'd be happy to take them. My SQL-genius superior tried the ALTER and gave up after two minutes, so it's probably hopeless, but I'm willing to try anything.
-
Ahh the database remains in Restoring... in SSMS?
Give the following command a go once you have done your original restore:
RESTORE DATABASE dbName WITH RECOVERY
- dbName (Restoring...) in SSMS/QA denotes that the restore has been done with NO RECOVERY, if you want to database not to be writable but readable then you can do RESTORE DATABASE dbName WITH STANDBY=.... if you want it to be in normal mode then use RECOVERY in your restore statement..
- James
--
James Moore
Red Gate Software Ltd -
New error when I try the WITH RECOVERY:
Msg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
[...]
There's a log file in the backup, I think...
Regarding the NORECOVERY option: I wasn't sure what we should use. If we are able to actually deploy the recovered data on the server, there's new data being written to it now--there's a big hole in it because we lost the backup; the data stops at January 25th and starts again on April 18th. This backup should have everything up until sometime on the 17th, or wherever the corrupted data begins, I guess. If we do recover some data from this database backup and try to load it on the server, I wasn't sure which option we should use to be sure that we don't wipe out the current data, too. :/
Thanks a lot for your help, James.
-
Do you have the original mdf file at all not the one in the backup? And do you keep multiple backups?
-
The only mdf we had, for some reason, had data up to January 25th. We typically keep multiple backups, too, but for reasons not fully explained to me (and honestly, I don't want to know) we only had a single backup overwriting itself at 3 AM every morning. When I asked about this, the response I got was essentially "well, you're the new DBA, and it's up to you to implement things like this!" Not really the answer I was looking for.
-
Still getting this error:
Msg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
[...]
RESTORE LOG gives me the same error, too. Any ideas, anybody? Everyone else I work with has taken a single look at this and given up. I'm giving it until Friday afternoon.
-
Can you post the exact commands you have run to get to the state you are in (block out server/database/paths if you want) ?
- James
--
James Moore
Red Gate Software Ltd -
All we've really done:
RESTORE DATABASE cms FROM (file) WITH NORECOVERY
We get cut off at 90% with the first error I posted:
[...]
90 percent processed.
Msg 3183, Level 16, State 2, Line 1
RESTORE detected an error on page (0:978) in database "cms" as read from the backup set.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
That's what got us hanging at (Restoring). Then:
RESTORE DATABASE cms WITH RECOVERY
That gets us:
Msg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
In the meantime I've tried STOPAT, CONTINUE_AFTER_ERROR, and RESTORE LOG. The first two didn't work and RESTORE LOG gives us the same log error. I'm going to look on the server itself to see if I can find any other leftover files from this backup, but everyone is insisting that it's hopeless at this point--and it very well may be, but I'm open to any further suggestions (it seems so close!).
Sorry if I'm being unclear at all; let me know if there's anything else I can clarify. I'm a total n00b at this.
-
What is the extact error if you run
RESTORE DATABASE cms FROM (file) WITH CONTINUE_AFTER_ERROR, NORECOVERY
- James
--
James Moore
Red Gate Software Ltd -
The error in full:
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Msg 3241, Level 16, State 37, Line 1
The media family on device (filename) is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
-
What happens if you run CHECK DB on the resulting database?
- James
--
James Moore
Red Gate Software Ltd -
I'm not able to, either via drop-down selection or putting the database name into the query:
Msg 927, Level 14, State 2, Line 1
Database 'cms' cannot be opened. It is in the middle of a restore.
-
OK lets try a slightly different approach,
I first want you to detach the database using sp_detch_db
EXEC sp_detch_db @dbname='cms'
We will then try and get SQL Server to rebuild the log (I have never done this in this situation but it might work)
EXEC sp_attach_single_file_db @dbname = 'cms',
@physname = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CMS_Data.mdf';
Where @physname is the actual path to your mdf file.
- James
--
James Moore
Red Gate Software Ltd
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply