February 3, 2011 at 5:09 am
The log file we had on the working database was only 1MB, while our database was 12GB.
How can we continue without the logfile?
February 3, 2011 at 5:21 am
Shut down SQL, delete the log file, restart SQL.
Once SQL starts, check the state of the database in question (select name, state_desc from sys.databases), and any errors in the error log relating to this DB.
The chances of a successful recovery are dropping...
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
February 3, 2011 at 6:22 am
The state of the database is
RECOVERY_PENDING
Another thing,
The original files on the backups show that the files were called
UTraining_Data.mdf
Training_1_Data.ndf
Training_log.LDF
But when I peep at the .mdf file I'm relying on for recovery, its shows the three files are called
Jox.ndf
Jox.log
Jox_1.ndf
Respectively.
February 3, 2011 at 6:48 am
And the messages in the error log please.
Don't worry about the names of the files. That's hardly a concern at this point.
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
February 3, 2011 at 9:23 am
Thank you GilaMonster for the patience.
When run this command:
select name, state_desc from sys.databases
I get this status
RECOVERY_PENDING
Then i've run this commands
use master
go
sp_configure 'allow updates', 1
reconfigure with override
go
I get: Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.
Then I run the command:
select status from sysdatabases where name = 'dbname'
Result: 65536
Then I run the command:
update sysdatabases set status= 32768 where name = 'dbname'
Result:
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
Then I run the command:
ALTER DATABASE dbname SET EMERGENCY
Result:
Msg 5173, Level 16, State 1, Line 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Msg 5173, Level 16, State 1, Line 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
So I am stuck there
February 3, 2011 at 9:30 am
As you absolutely sure that all of the files do belong to the same database (that's the mdf and the ndf)? The error indicates that they do not. Unless you have the mdf and ndf from the same database, there is not possible way to recover this.
Can you also open the SQL error log and find all messages relating to this database.
Do not do anything else at this point
p.s. There's a reason I work slow-step by slow-step when answering these and it's because it's possible to make things worse by doing certain operations (like trying SQL 2000 specific fixes on SQL 2008)
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
February 3, 2011 at 10:23 am
Thanks, I heed your advice. OK, I had saved a version of the files on a different location.
I am not very sure if the files I'm working on are from the same database, but I have no option than to try them, and even try some combinations on the copies.
Here is the SQL errorlog.
-----
2011-02-03 19:31:23.12 spid64 Setting database option EMERGENCY to ON for database komu3.
2011-02-03 19:31:23.17 spid64 Starting up database 'komu3'.
2011-02-03 19:31:23.20 spid64 Error: 5173, Severity: 16, State: 1.
2011-02-03 19:31:23.20 spid64 One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
2011-02-03 19:31:23.20 spid64 Error: 5173, Severity: 16, State: 1.
2011-02-03 19:31:23.20 spid64 One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
2011-02-03 19:31:23.20 spid64 Error: 5105, Severity: 16, State: 1.
2011-02-03 19:31:23.20 spid64 A file activation error occurred. The physical file name 'G:\test\Jox_1.ndf' may be incorrect. Diagnose and correct additional errors, and retry the operation.
2011-02-03 19:47:15.15 spid66 Setting database option OFFLINE to ON for database komu3.
2011-02-03 19:47:49.71 spid66 Setting database option ONLINE to ON for database komu3.
2011-02-03 19:47:49.75 spid66 Starting up database 'komu3'.
2011-02-03 19:47:49.79 spid66 Error: 5173, Severity: 16, State: 1.
2011-02-03 19:47:49.79 spid66 One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
2011-02-03 19:47:49.81 spid66 Error: 5173, Severity: 16, State: 1.
2011-02-03 19:47:49.81 spid66 One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
2011-02-03 19:47:49.81 spid66 Error: 5105, Severity: 16, State: 1.
2011-02-03 19:47:49.81 spid66 A file activation error occurred. The physical file name 'G:\test\Jox_1.ndf' may be incorrect. Diagnose and correct additional errors, and retry the operation.
2011-02-03 19:47:49.82 spid66 Error: 5173, Severity: 16, State: 1.
2011-02-03 19:47:49.82 spid66 One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
2011-02-03 19:47:49.82 spid66 Error: 928, Severity: 20, State: 1.
2011-02-03 19:47:49.82 spid66 During upgrade, database raised exception 945, severity 14, state 2, address 017195C8. Use the exception number to determine the cause.
February 3, 2011 at 10:40 am
The ndf is definitely from a different database as the mdf.
It also looks like the database was SQL 2005 or 2000, not a 2008 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
February 4, 2011 at 10:56 am
Hi,
I have tried to combine the two files that look most likely. The database is surely an sql 2008 database migrated two years ago from Navision 4.0
Here is the current status
ALTER DATABASE dbname SET emergency
2011-02-04 20:30:23.30 spid53 FCB::Open failed: Could not open file c:\magic\unes.ndf for file number 1. OS error: 2(failed to retrieve text for this error. Reason: 15100).
2011-02-04 20:30:23.41 spid53 Error: 5161, Severity: 16, State: 1.
2011-02-04 20:30:23.41 spid53 An unexpected file id was encountered. File id 3 was expected but 1 was read from "c:\magic\unes.mdf". Verify that files are mapped correctly in sys.master_files. ALTER DATABASE can be used to correct the mappings.
2011-02-04 20:30:23.41 spid53 Error: 5105, Severity: 16, State: 1.
2011-02-04 20:30:23.41 spid53 A file activation error occurred. The physical file name 'c:\magic\unes.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation.
2011-02-04 20:30:23.42 spid53 Error: 928, Severity: 20, State: 1.
2011-02-04 20:30:23.42 spid53 During upgrade, database raised exception 945, severity 14, state 2, address 00000000013C2DA7. Use the exception number to determine the cause.
February 4, 2011 at 11:07 am
Still not the right files in the right order.
I suspect at this point you may be best off restoring the last backup you have.
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
February 4, 2011 at 1:00 pm
GilaMonster (2/3/2011)
The ndf is definitely from a different database as the mdf.It also looks like the database was SQL 2005 or 2000, not a 2008 database.
Hi Gail.. I'm just following along at home. Can you tell me how you can tell that the database was from 2005 or 2000?
Thanks,
Shaun
February 4, 2011 at 1:08 pm
shaun.stuart (2/4/2011)
Hi Gail.. I'm just following along at home. Can you tell me how you can tell that the database was from 2005 or 2000?
2011-02-04 20:30:23.42 spid53 During upgrade, database raised exception 945, severity 14, state 2, address 00000000013C2DA7. Use the exception number to determine the cause.
That makes it look to me like SQL was running a version upgrade. Not 100% sure, but does look that way.
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
February 4, 2011 at 1:10 pm
Doh! The very last line... I completely overlooked it.. Thanks!
Shaun
February 5, 2011 at 9:52 am
We certainly reinstated the backup two days ago, but still, there is some very important data we need from the crashed DB. We are going to look for tools that can recover files even if overwritten from a certain point.
Thank you all the same for the support, and wisdom. I've certainly learned a mountain from the incident and the interactions.
Thanks again.
February 5, 2011 at 10:07 am
lordmwesh (2/5/2011)
We are going to look for tools that can recover files even if overwritten from a certain point.
By all means try, but I have not seen a database recovered by such file recovery tools having been usable, not even when recovered right after delete.
Was the DB structured with just one filegroup or two?
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
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply