Restoring a corrupted database?
-
Detaching works. Rebuilding does not.
Msg 5172, Level 16, State 15, Line 1
The header for file '(filename)' is not a valid database file header. The FILE SIZE property is incorrect.
The thing about this is that our mdf file (for the cms database) is inside a larger file containing all the backups from the evening when this occured. The file doesn't have a file extension and I haven't been able to open it to browse to the mdf file and extract it, or anything like that. The only way I know it's there is because, when running the Restore wizard thing, you can actually see all the databases available inside it when you select the device to restore from.
I have the filename of the mdf file within the backup file, but I can't run the rebuild from there, either:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "(location)/Daily SQL Full Backup/(hugename).mdf" failed with the operating system error 123(The filename, directory name, or volume label syntax is incorrect.).
I get the same error if I try (and I had to try this) renaming the no-extension file to .mdf.
-
The MDF file is not the backup file but the actual data file SQL Server uses - the bak file will be inside the mediaset, however we need the mdf file - if you run
USE master;
GO
SELECT * FROM sysfiles;
You will get a list of files, the path of this is possibly where the cms data file will be - if you browse to the path of those files then there will probably be a file called cms_data.mdf - if not search your harddrive for any file ending in mdf and see if somthing similar turns up - this is nothing to do with your backup its where sql server stores the file and that is the argument to sp_attach_single_file_db not your backup device/file.
- James
--
James Moore
Red Gate Software Ltd -
OH! Okay, my apologies--I misunderstood what we were trying to do. I located the files.
This is becoming quite interesting. The result:
File activation failure. The physical file name "D:\(folder)\cms_Log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'cms'. CREATE DATABASE is aborted.
The D:\(folder)\ location is actually where these databases were stored on the server on which they originally resided. The file name is obviously incorrect because I don't have a drive named D: (nor could I have one without partitioning).
Any ideas? I could, if necessary, log back onto the server these files came from and make this attempt there--I would just have to be very careful, because our cms database is being written with new data that I would likely be murdered for losing.
-
Are you trying to restore this database to the same server the backup was taken from?
If you dont have a D drive then you will have to run the restore again specifying a different path for the log file - have a look at using WITH MOVE Cms_LOG TO some path http://msdn2.microsoft.com/en-us/library/aa238405(SQL.80).aspx or you can probably do this through the EM UI.
If you cant do it through the UI then you might want to have a look at RESTORE HEADERONLY followed by a RESTORE FILELISTONLY to get the information you need to do a RESTORE with the correct MOVE arguments.
- James
--
James Moore
Red Gate Software Ltd -
Just as a reference your restore command would like somthing like this:
RESTORE DATABASE cms FROM .... WITH CONTINUE_AFTER_ERROR, MOVE 'CMS_DATA' TO 'c:\CMS_DATA.mdf', MOVE 'CMS_LOG' TO 'c:\CMS_LOG.ldf'
Where CMS_DATA and CMS_LOG are the logical filenames which make up the cms database.
- James
--
James Moore
Red Gate Software Ltd -
I think I mentioned this before--I'm trying to restore this to my own computer first, just be sure a) that it works and b) that I don't wipe out any of the data currently being written to said database on its server.
I'll give this a shot.
-
-
That's okay!
I tried the RESTORE command with MOVE to get both cms_Data and cms_Log to a new location (my desktop). Both showed up. However, I still got the error previously noted at 90%. I tried the detach and attach again, but on the attach it's still looking for the nonexistant D drive instead of in the same location as where the cms_Data file is now.
After some searching I tried a sp_attach_db (instead of single_file_db) and actually specified the log location. SQL seemed to be working for a moment, then there was this:
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'cms'. CREATE DATABASE is aborted.
Msg 9004, Level 21, State 1, Line 1
An error occurred while processing the log for database 'cms'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
NOOOOO!
-
I ended up temporarily moving the cms_Data and cms_Log files off of my desktop, and tried the RESTORE again (with RECOVERY, CONTINUE_ON_ERROR, and MOVE etc). I'm back to this again:
Msg 3241, Level 16, State 37, Line 1
The media family on device 'C:\(backup)' is incorrectly formed. SQL Server cannot process this media family.
And we're right back to the database hanging at (Restoring). Sigh.
-
If you run the restore command using RECOVERY rather than NORECOVERY what does the database show once the restore has failed?
- James
--
James Moore
Red Gate Software Ltd -
RECOVERY and NORECOVERY both give me the error pasted above.
-
Its more the state of the database once the restore is finshed which I am interested in - I was wondering if you use RECOVERY rather than NORECOVERY if the database ends up in suspect rather than Restoring...
- James
--
James Moore
Red Gate Software Ltd -
Both leave the database sitting at Restoring. I've yet to see anything marked as Suspect.
-
Most likely not but check to see mdf becomes available during the recovery. If so copy it to another location (might not let you since may be locked). However if you can get it you can try sp_attach_single_file_db to see if you can swing past.
-
Antares: Trying a single_file_db will not go through as I'm prompted for the recovery of our log file, as well. That's why I switched over to sp_attach_db, as I could specify the location of both. That got me closer, but still no cigar.
We're at three pages on this thread now, so unless there's anything else I might be able to try doing, I think I'm about ready to say the data's hopelessly corrupted and call it a day. I genuinely appreciate all the help I've been given, though
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply