Monday, March 19, 2012

Cannot open database

My mdf file was deleted. I was able to restore the said file but unfortunately I can no longer open it.

I have also noticed that the recovered mdf file was smaller than the file before it was deleted.

What can I do to open the database file? I have tried the following lines using osql utility.

1> use db1sql1
2> go
Msg 946, Level 14, State 1, Server JDI11-31, Line 1
Cannot open database 'db1sql1' version 525. Upgrade the database to the latest version.

Try detaching the database and then reattaching - it should get upgraded as part of the attach process.|||Hi Paul!

I got the following message upon execution of sp_attach_db.

Msg 5180, Level 22, State 1, Server JDI11-31, Line 1
Could not open FCB for invalid file ID 0 in database 'db1sql1'.
Converting database 'db1sql1' from version 525 to the current version 539.
Database 'db1sql1' running the upgrade step from version 525 to version 526.|||

After the message about going from 525 to 526 did it stop or print anything else?

|||Nothing else printed and I was returned to osql prompt.
|||

So what's happened is that a page in the database has a corrupt page header, such that the next-page pointer has a 0 in the file-ID part of the pointer. 0 is an invalid file number and so the upgrade process has to terminate.

There's nothing that can be done at this stage (documented or undocumented) unless you have any SQL Server backups - sounds like you had a file system backup?

|||The sad truth is that I don't have any backups. Is there a way to retrieve the schema?|||The unfortunate answer is no - you can't get anything out of the database unless its attached - the schema is wholly contained within the database.|||I think the database has been attached. Because when I tried to attached the database I got the following message:

Msg 1801, Level 16, State 3, Server JDI11-31, Line 1
Database 'db1sql1' already exists.

Unfortunately, upon executing "use db1sql1" I got the following message:

Msg 946, Level 14, State 1, Server JDI11-31, Line 1
Cannot open database 'db1sql1' version 525. Upgrade the database to the latest
version.

Is there a way I can transfer the database data into new database?

|||ok - so the sp_attach_db you executed in the previous mail failed, but at least attached the DB in some form. There's still no way for you to get anything from the database - its metadata is corrupt in some way (as I explained previously) which is preventing it being attached and upgraded correctly.|||Is there a tool that I can use to repair the database?
|||

No - as I explained before, the database isn't accessible because the corruption is preventing the upgrade steps from completing. This means that no SQL Server tools can connect to the database.

Your only recourse in this case is to restore from a backup.

No comments:

Post a Comment