unable to attach database 1

[LoJACK]

Hi everyone,

My database was accidently deleted after my system crashed. I was able to recover the .MDF and LDF files using a data recovery software.

The problem that I'm having is that when I try to attach the recovered file to the SQL server using the Enterprise Manager that it is giving me this error message: "The file you've specified is not a valid SQL server database file."

Does anyone know how I can go about to attach the database or recover the data in the files? Any help would greatly be appreciated.

Thanks,
LoJACK

 

[LogicalmanUS]

LoJACK,

This sounds drastic, but may even work. Create the database empty.
Stop the service.
Replace the MDF LDF files with the old files (making sure the old files have the same names as the files they are replacing.
Start the service and open EM

I just did this, and it worked a treat.

Logicalman

 

[LoJACK]

I try that Logicalman, but it didn't work. The I can see the database in the SQL Server but it has a "(Suspect)" next to the database name and when I try to open it to view the tables it just has a "(No items)"

thanx,
LoJACK

 

[ecobb]

Now that you've gotten it restored this far, maybe this will help. It's a Microsoft article on what to do if you have a "Suspect" database.

http://support.microsoft.com/default.aspx?scid=kb;en-us;180500&Product=sql2k

Hope This Helps!

Ecobb
- I hate computers!

 

[LogicalmanUS]

LoJACK,

Sorry, I'm out of ideas at the moment.

The only other thing I can think of is to reset the suspect flag and see if you can, at least, see the objects or use a DTS Import wizard to recover the data to another database.

You will need to use sp_resetstatus to reset the flag, but this is fraught with danger. Before using you need to lookup 'Resetting the Suspect Status' in BoL to get the full sp.

Logicalman

 

[LoJACK]

I'm going to give it a try tommorrow and I'll let you guys know.

Thanx,
LoJACK

 

[LoJACK]

I've try resetting the Suspect Status on the database but got the erroe message: "Database CCFundManagement_US does not exist!"

LoJACK

 

[JayKusch]

Do you still see the database in Enterprise Manager and does it still read "Suspect"

Thanks

J. Kusch

 

[LoJACK]

I'm still able to see the database in the EM and it does still read "Suspect" next to the database's name. However I do not see the database inside Query Analyzer.


Thanks,
LoJACK

 

[SQLBill]

I had something like this happen to me.

Try this:
Select the server and choose Refresh.
That should get rid of the entry.
If it does, just Attach the database again.

If not, try this:
Ignore the entry for your database.
Attach the database.
This should give you two entries for the same database in Enterprise Manager.
Do a Refresh.
Now you should only have one.

-SQLBill

 

[JayKusch]

Here ya go ... I also created a FAQ on this one. Should be out by tomorrow. Hope it helps.


1. Firstly look in the SQL Server Logs in Enterprise Manager(EM). To get there, start EM and drill down from the Database Server … Management … SQL Server Logs. There should be multiple logs starting with Current and then followed by 6 or more archived logs.

2. Review all recent Error Log(s). There WILL be an indication here as to why the database has been marked suspect. You need to fix whatever the problem is first (i.e. missing file, permissions problem, hardware error etc.)

3. Then, when the problem has been fixed and you're either sure that the data is going to be ok, or you have no backup anyway, so you've nothing to lose, then change the database status to normal and restart SQL Server.

4. To change the database status we will us the following store procedure: sp_resetstatus.
The steps are as follows:

a. USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
b.
sp_resetstatus
GO
-- Example: sp_resetstatus ‘Washington’

5. After the procedure is run, immediately disable updates to the system tables:

a. sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO

6. Stop and Restart ALL SQL Server Services

7. If the database still goes back into suspect mode, and you can't fix the original problem, and you have no recent backup, then you can get information out of the database by putting it into Emergency Mode. If you do this, extract the data/objects out with DTS and rebuild the database. Note that the data may be corrupt or transactionally inconsistent. You WILL NOT be able to use this instance of the database after it is put in to Emergency Mode besides pulling data out of it !!!

8. Issue the following command to put the database into emergency mode:

a. USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO

b. UPDATE master..sysdatabases
SET status = 32768
WHERE name = 'DatabaseName'

9. Stop and Restart ALL SQL Server Services.

10. We are now ready to pull whatever data we can out of the tables in the corrupt database. Remember, some tables may be corrupt, thus you may have to play with various T-SQL statements to get the data out. First try DTS …

11. These are the steps necessary to export data out of the corrupt database into the new:

a. Create a new production DB, or a temp DB, to have a place to export whatever data we can out of the corrupt db.

b. Start a DTS operation by going into EM and drilling down to “Data Transformation Services” … “Local Packages”.

c. Open a NEW DTS package by right-mouse clicking …

d. When DTS opens, choose “Copy SQL Server Objects Task” from the Connection Icons. Enter in a description like “Export Corrupt Data”. Enter in the SA/pass combination as well as the CORRUPT database from the drop-down.

e. Select the “Destination” Tab. ”. Enter in the SA/pass combination as well as the PRODUCTION database from the drop-down.

f. Select the “Copy” Tab. UNCHECK the “Create destination objects” box. UNCHECK the “Copy all objects” box and then Click on the “Select Objects” Button. This brings up the “Select Objects” screen.

g. CHECK ONLY “Show all tables” like shown above. Then check each table that needs to be exported. If ALL tables need to be export, Click on the “Select All” button. Click OK.

** If ALL objects are to be recovered, Select ALL Objects by check marking them and then click on the “Select All” button. This will grab everything possible.

h. Click OK again and we are done creating this task. Now we execute the package by Clicking the green arrow on the menu bar.

12. Restore Data Issue: Restoring the data into the Production database is dependent on what time of day it is. If it is during “Hot” times, high playing times, restore the data during a slow period or close of gaming day!

If Microsoft Tech support is to be called, it is advisable to get the log files ready to be emailed to the tech for review. The process to accomplish this is as follows:

1. Go to the Bin folder located under the SQL Server installation folder. In this folder you will find an application called SQLDIAG.exe.

2. Execute SQLDIAG.exe. This app is “suppose” to zip all the log files into a file called SQLDIAG.txt in the Log folder under the SQL Server installation folder. This operation did NOT work for us.

3. We went directly into the Log folder of SQL Server and used PKZip to zip the files. We then emailed them directly to the tech.



Thanks

J. Kusch

 

[LoJACK]

I got up to step 11-f and I got this error message:

Error 945: "Database cannot be opened dur to inaccessible files or insufficient memory or disk space."


I check the server errorlog:
"The header for file 'D:\Microsoft SQL Server\MSSQL\data\CCFundManagement_US2_Data.MDF' is not a valid database file header. The PageAudit property is incorrect."

thanx,




Thanks,
LoJACK

 

[JayKusch]

Just a side question ... Are the MDF and LDF from a SQL Server 2000 installation AND are you trying to attach them, in order to get your restore to work, on to a SQL Server 7 box???? Just Wondering ...

Thanks

J. Kusch

 

[JayKusch]

By the error message you relayed ... you may also be experiencing a couple of other possibilities...

The errors you see are caused due to:

1) You are trying to attach a SQL2000 DB to a SQL7 server. You can attach SQL7 to
SQL2000 but not the other way around.
2) You have a DPT controller whose specs are mentioned in

http://support.microsoft.com/suppor...s/q268/4/81.asp

.
3) Your master, user databases are corrupt.

Depending on what your cause of error is,from the above three options:-

Run DBCC consistency checks on Master and user databases to make sure they are not
corrupt. Review the application and system event logs for CHKDSK messages; if found
and if logs suggest running CHKDSK,then FIRST copy off EVERY database file and THEN
run CHKDSK /f. If you have corruption, then restore from good known backup.

Please note that rebooting the server (while CHKDSK /f is being run) can cause TOTAL
data loss on a drive.


Thanks

J. Kusch

 

[LoJACK]

it is from a SQL Server 2000 and I try to attach it.

LoJACK



Thanks,
LoJACK

 

[JayKusch]

One more time ... LOL ... I know the MDF and LDF came from a SS2K installation BUT ... are you trying to attach the files on an SS2K installation too?

Thanks

J. Kusch

 

[LoJACK]

I'm trying to attach the database on a SQL Server 2000 too.




Thanks,
LoJACK

 

[LogicalmanUS]

JayKusch,

Here's a star, just for the effort your putting into this, wonderful to see and I'll just keep a watch on it.

We've found a similar suspect DB on one of our development servers, and after all else failed, we all chose to ignore it! But I'll be having another go...once you get this one solved (LOL)

Logicalman