Help- emergency
-
I have just deleted a clients database by mistake ( working too many hours this weekend!). I have recovered the actual files with Ontrack file recovery software.
I have tried to attach the mdf but SQL says it's not a valid sql database file. I have also created a new database of the same name , stopped sql, copied over the original files and re-started sql . SQL ( 2000) then marks the datbase as suspect.
Does anyone have a possible
thx
Stressed & Desperate.
No backups available . No lectures please... i've just learn't the VERY hard way !!
-
It sounds like there may be corruption in the files or files are missing. Are you sure you were able to recover all the files (include any .ldf and .ndf files)? If any of the files are missing, SQL Server would continue to mark it suspect.
First, make sure you have a copy of the recovered files stored somewhere else. If the following doesn't work, you still have the files when you call Microsoft Support. They may be able to help you even considering the situation, though, of course, they will charge you for the incident.
Before going down this path, check your SQL Server error log to see what applicable messages are there. They may give you insight that a file is indeed missing or the like.
As to changing the status of the database... You can reset the status of the database using the sp_resetstatus stored procedure. However, there's no guarantee that everything is okay. You'll have some DBCC commands to run to check out everything.
This is detailed in the Workaround section of the following KB article. The article assumes you were just missing a device at startup but have it available now. However, what you're trying to do is similar. Hopefully you'll be able to get your data back.
PRB: Missing device causes database to be marked suspect (180500)
K. Brian Kelley
@kbriankelley -
Brian ,
Thanks for the quick reply. but resetting the status does not seem to help .After restarting MSSQL it is still marked as supspect so I cannot run an queries against this.
Below are the logs.
Server Process ID is 5232.
2005-11-20 13:37:08.51 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2005-11-20 13:37:08.53 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2005-11-20 13:37:08.56 server SQL Server configured for thread mode processing.
2005-11-20 13:37:08.57 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2005-11-20 13:37:08.60 server Attempting to initialize Distributed Transaction Coordinator.
2005-11-20 13:37:09.68 spid4 Starting up database 'master'.
2005-11-20 13:37:09.92 server Using 'SSNETLIB.DLL' version '8.0.766'.
2005-11-20 13:37:09.92 spid5 Starting up database 'model'.
2005-11-20 13:37:09.93 spid4 Server name is 'WEBSVR'.
2005-11-20 13:37:10.01 spid8 Starting up database 'msdb'.
2005-11-20 13:37:10.01 spid9 Starting up database 'pubs'.
2005-11-20 13:37:10.01 spid11 Starting up database 'Northwind'.
2005-11-20 13:37:10.01 spid12 Starting up database 'saleslogix'.
2005-11-20 13:37:10.01 spid10 Starting up database '62NOSP1'.
2005-11-20 13:37:10.04 server SQL server listening on 66.xxx.203.xxx: 1433.
2005-11-20 13:37:10.04 server SQL server listening on 127.0.0.1: 1433.
2005-11-20 13:37:10.04 server SuperSocket Info: Bind failed on TCP port 1433.
2005-11-20 13:37:10.09 spid10 The header for file 'C:\Program Files\Microsoft SQL Server\MSSQL\data\62NOSP1_Data.MDF' is not a valid database file header. The PageAudit property is incorrect.
2005-11-20 13:37:10.18 spid5 Clearing tempdb database.
2005-11-20 13:37:10.35 server SQL server listening on TCP, Named Pipes.
2005-11-20 13:37:10.35 server SQL Server is ready for client connections
-
It looks like the file was corrupted then, the main one (the .mdf). based on:
2005-11-20 13:37:10.09 spid10 The header for file 'C:\Program Files\Microsoft SQL Server\MSSQL\data\62NOSP1_Data.MDF' is not a valid database file header. The PageAudit property is incorrect.
At this point, probably your only option is to contact Microsoft and see if they can work some "magic."
K. Brian Kelley
@kbriankelley -
You could try putting your database into emergency mode this will then allow you to bcp your data out and you can then re create the database and bcp the data into the new databases.
to do this run the following
SP_CONFIGURE 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
UPDATE master..sysdatabases set status = -32768 WHERE name = '62NOSP1'
GO
SP_CONFIGURE 'allow updates', 0
RECONFIGURE WITH OVERRIDE
Good luck.
Hope this helps...
Ford Fairlane
Rock and Roll Detective
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply