Attaching database & DBCC CHECKTABLE problem...

seananderson · March 30th, 2007, 09:04 AM

In Enterprise manager, I right-click on 'databases' (beneath Local) and select to attach a database, select the file (which resides with other SQL database files), set the 'Attach As' to the name I want (which is taunton) and the owner to sa. When I click on Okay, I get the following error:

Microsoft SQL-DMO (ODBC SQLState: HY000)
Error 602: Could not find row in sysindexes for database ID 31, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes

Switched to SQL Query analyzer and executed:

DBCC CHECKTABLE ('master.dbo.sysindexes')

Which returns:

DBCC results for 'sysindexes'.
There are 104 rows in 4 pages for object 'sysindexes'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Any ideas of what's next?

I have detached/attached plenty of databases before without this and cannot think that there's been any significant changes to my server, for which I am on the only one who has access (as it's on my laptop).

Thanks in advance for any help you could offer...

Regards,

Sean Anderson

robprell · March 30th, 2007, 11:27 AM

could be hard drive errors causing it. I suggest tryint to back up your data and restore it to another computer. Se if you can move the data and if you can check if you still have the errros you describe.

seananderson · April 2nd, 2007, 05:09 AM

The database came off a CD, which I copied to my local drive. Checked everything, I can move, rename, delete, etc., and there's plenty of space on the drive. Also, there's no attributes set (such as read-only, system, etc.,).

Regards,

Sean Anderson

happygv · April 2nd, 2007, 07:19 AM

What robprell means by hard drive errors is there could be some errors with hard drive partition where you are trying to attach it. Try attaching it with some other logical partition if any you have. I doubt if the MDF file is corrupted.

What is the SQL server version of the MDF/LDF files that you are trying to attach? What version of SQL server are you using in which you are trying to attach? Is that you are trying to attach a sql sever 2005 version on sql sever 2000?

Just a guess, try creating the database with the same name from the Enterprise manager under same path as that of the original one. Then stop sql service and overwrite the newly created mdf and ldf files with the ones you got off the CD. See if this solves the issue.

cheers.

_________________________
- Vijay G
Strive for Perfection

seananderson · April 2nd, 2007, 07:25 AM

I don't have another partition to work from. Will try one of the network drives when I am next in the office.

I tried the idea of creating the database and overwriting the files and when I did, the entry in SQL Enterprise Manager said the database was 'suspect' and that it contained no items.

Have asked my customer to resupply the file as a SQL Backup and will try it from there.

Regards,

Sean Anderson

happygv · April 2nd, 2007, 08:29 AM

Also try this on the sql query analyser from the database that you attached.

Select db_name(31) -- verify if this is the name of the database that you attached.
The table reference that is shown in the error seems to be that of sysobjects.
Index Id shown in the error seems to be that of a clustered index.

_________________________
- Vijay G
Strive for Perfection