Database can't be recovered? Any help would be appreciated!!!
-
I have a database, recently the W2K server crashed. No backup, only mdf, ldf files
I tried to restore the DB on a new Win2K5 server, I tried:
1. directly attached mdf, failed
2. attached mdf and ldf, failed
3. created a new DB with the same name and then take it offline
4. replace the mdf, failed
5. replace both, failed
What can I do now?
The error says:
TITLE: Microsoft.SqlServer.Smo
------------------------------
Set offline failed for Database 'CheckListDB_Data'.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An error occurred while processing the log for database 'CheckListDB_Data'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
Database 'CheckListDB_Data' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9004)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=9004&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
-
Can you explain what you ran, or what errors you got? Also, is this SQL 2005, and did you mean a new SQL 2005 instance?
If it's one MDF, did you run: sp_attach_single_file_db? (http://msdn.microsoft.com/en-us/library/ms174385.aspx)
Or did you use SSMS?
Chances are there's either some error returned that might help us debug, or it's possible that you might be missing a file. Are you sure there were no NDF files?
-
The new server is SQL 2005, the damaged files are from 2000
-
Steve Jones - SSC Editor (7/19/2011)
Can you explain what you ran, or what errors you got? Also, is this SQL 2005, and did you mean a new SQL 2005 instance?If it's one MDF, did you run: sp_attach_single_file_db? (http://msdn.microsoft.com/en-us/library/ms174385.aspx)
Or did you use SSMS?
Chances are there's either some error returned that might help us debug, or it's possible that you might be missing a file. Are you sure there were no NDF files?
I did use SSMS, the error message I posted is what I got so far
Thanks
-
It is not a good idea to try and upgrade a damaged file. Not got a 2000 server to attach them to?
Try this
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
If it fails, tell us exactly at what point it failed and with what message.
Don't suppose there's a backup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (7/19/2011)
It is not a good idea to try and upgrade a damaged file. Not got a 2000 server to attach them to?Try this
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
If it fails, tell us exactly at what point it failed and with what message.
Don't suppose there's a backup?
Error 9904 was returned when I tried to attach the damaged mdf in a healthy SQL 2000 server.
-
Have you tried the create database options that rebuild the log file?
Create Database For Attach_Rebuild_Log
(Check MSDN or BOL for the full syntax.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Sorry I have to go, I am being pulled out to another case, I haven't finished reading the "http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/"
I will read it once I am back, but can you simply tell me here step by step on what should I do?
Thank you so much guys, any help is greatly appreciated!!!!!!!!
-
GSquared (7/19/2011)
Have you tried the create database options that rebuild the log file?Create Database For Attach_Rebuild_Log
(Check MSDN or BOL for the full syntax.)
Can you elaborate? Can you provide your idea in code? Thank you
-
halifaxdal (7/19/2011)
Sorry I have to go, I am being pulled out to another case, I haven't finished reading the "http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/"I will read it once I am back, but can you simply tell me here step by step on what should I do?
It's in my blog post, step by step.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GSquared (7/19/2011)
Have you tried the create database options that rebuild the log file?Create Database For Attach_Rebuild_Log
(Check MSDN or BOL for the full syntax.)
Unlikely to work unless the DB was cleanly shut down. That's what would have been run by SSMS when just attaching the mdf file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Gila's post is very elaborate, a quick read will help you out, so please read her post and let us know if your problem has been solved.
-
I tried to follow the post: http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
I encountered this error when I do:
DBCC CHECKDB(CheckListDB_Data, REPAIR_ALLOW_DATA_LOSS)
Msg 946, Level 14, State 1, Line 1
Cannot open database 'CheckListDB_Data' version 539. Upgrade the database to the latest version.
So, I tried to upgrade the database first, I downloaded the upgrade advisor from Microsoft but again another error pops up:
Unable to connect to server. Reason: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
I double checked the instance name, the name I input is correct. I did twice both remotely and locally, the advisor failed to recognize the instance's name which is the same of the local server and machine's name.
I am stuck here now.
What can I do now?
Thank you very much.
-
halifaxdal (7/20/2011)
So, I tried to upgrade the database first, I downloaded the upgrade advisor from Microsoft but again another error pops up:That's not how you upgrade a DB. Upgrade advisor just advises on compatibility issues.
A DB is upgraded by attaching it to a higher version. As I said before, it's a bad idea to try and upgrade a damaged database. Try attaching and fixing on SQL 2000 because that DB will not upgrade due to the damage.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (7/20/2011)
halifaxdal (7/20/2011)
So, I tried to upgrade the database first, I downloaded the upgrade advisor from Microsoft but again another error pops up:That's not how you upgrade a DB. Upgrade advisor just advises on compatibility issues.
A DB is upgraded by attaching it to a higher version. As I said before, it's a bad idea to try and upgrade a damaged database. Try attaching and fixing on SQL 2000 because that DB will not upgrade due to the damage.
I got error 9904 when doing this in SQL 2000
Viewing 15 posts - 1 through 15 (of 130 total)
You must be logged in to reply to this topic. Login to reply