Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jckstraw
Starting Member
2 Posts |
Posted - 2004-10-13 : 12:58:13
|
I am doing some freelance report development work for a company and they sent me an MDF file. I am receiving the following error when trying to attach using the following command.Command:EXEC sp_attach_single_file_db @dbname = 'REPORTS', @physname = 'C:\Reports_Data.mdf'Error Message:Server: Msg 906, Level 22, State 2, Line 1Could not locate row in sysobjects for system catalog 'Sysobjects' in database 'REPORTS'. This system catalog should exist in all databases. Run DBCC CHECKTABLE on sysobjects in this database.Connection Broken |
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-10-13 : 13:11:11
|
Are you attaching a .MDF file that was copied either with the database down, or after a sp_detach_db command? If you didn't, then I would bet your MDF is corrupt.If it has been copied while the database is hot, then the file could be corrupt and you are SOL. I've seen many strange errors on attaching databases that were not detached properly, and there is really no way to recover them easily.As a last resort I would try Kevin Sun's (Microsoft) approach to recovering a database without the logfile. It has been posted pretty often on usenet, and I'm sure it has been posted here as well. Atleast now it has. Here is the text:quote: ==========1. Back up the .mdf/.ndf files at first!!!2. Change the database context to Master and allow updates to system tables: Use Master Go sp_configure 'allow updates', 1 reconfigure with override Go3. Set the database in Emergency (bypass recovery) mode: select * from sysdatabases where name = '<db_name>' -- note the value of the status column for later use in # 6 begin tran update sysdatabases set status = 32768 where name = '<db_name>' -- Verify one row is updated before committing commit tran4. Stop and restart SQL server.5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the supsected db. The syntax for DBCC REBUILD_LOG is as follows: DBCC REBUILD_LOG('<db_name>','<log_filename>') where <db_name> is the name of the database and <log_filename> is the physical path to the new log file, not a logical file name. If you do not specify the full path, the new log is created in the Windows NT system root directory (by default, this is the Winnt\System32 directory).6. Set the database in single-user mode and run DBCC CHECKDB to validate physical consistency: sp_dboption '<db_name>', 'single user', 'true' DBCC CHECKDB('<db_name>') Go begin tran update sysdatabases set status = <prior value> where name = '<db_name>' -- verify one row is updated before committing commit tran Go7. Turn off the updates to system tables by using: sp_configure 'allow updates', 0 reconfigure with override Go============3. The final option is to get the data using bcp utility:Though some file may be lost, However, much of the data in your database isoften still available, albeit transactionally (and physically) inconsistent. Wecan access this data by setting the database status to bypass, or emergencymode. This is done by setting sysdatabases.status to -32768 for the database,after turning "allow updates" on. For example, use the following command: UPDATE SYSDATABASES SET STATUS=32768 WHERE NAME='DBNAME'After doing this, we can enter the database and SELECT the data or use BCP toget it out. We may encounter errors while doing this, but in most cases much ofthe data can be retrieved.Resolution=============Please follow the steps below to get the data out.0) Backup all the databases first!!!!1) Execute the scripts below in Query analyzeruse mastergosp_configure 'allow update',1goRECONFIGURE with overridegoUPDATE SYSDATABASES SET STATUS=32768 WHERE NAME='your_database_name'gosp_configure 'allow update',0goRECONFIGURE with overridego2) Using select, BCP or other tools to retrieve the dataHope it help!-kevinThis posting is provided ¡°AS IS¡± with no warranties, and confers no rights.
-ec |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-10-13 : 13:19:26
|
I just had another thought. Did they send you the file on CD? I would check to make sure that the Read Only bit is not set. from windows explorer, right click the file and select properties to check.-ec |
|
|
jckstraw
Starting Member
2 Posts |
Posted - 2004-10-13 : 15:00:18
|
hmmm...before I try any of the above (thanks for the feedback!!!!)I will try to see if I can get a new copy of the MDF file just in case it was corrupted, etc.The file was sent to me via email in a zip file along with some other documentation. As far as I know, the file was not read only. |
|
|
|
|
|
|
|