June 3, 2002 at 3:36 am
I'm trying to recover a db that is mark as suspect in SQL 7.0. Can anybody help me? the status of the db in the sysdatabases table is 1073742080 (I can't find this status in the online books).
June 3, 2002 at 4:29 am
Try using sp_resetstatus to clear the suspect marking and see if clears.
Excerpt BOL
quote:
sp_resetstatusResets the status of a suspect database.
Syntax
sp_resetstatus [ @DBName = ] 'database'
Arguments
[@DBName =] 'database'
Is the name of the database to reset. database is sysname, with no default.
Return Code Values
0 (success) or 1 (failure)
Remarks
sp_resetstatus turns off the suspect flag on a database. This procedure updates the mode and status columns of the named database in sysdatabases. The SQL Server error log should be consulted and all problems resolved before running this procedure. Stop and restart SQL Server after executing sp_resetstatus.
A database can become suspect for several reasons. Possible causes include denial of access to a database resource by the operating system, and the unavailability or corruption of one or more database files.
Permissions
Only members of the sysadmin fixed server role can execute sp_resetstatus.
Examples
This example resets the status of the PUBS database.
EXEC sp_resetstatus 'PUBS'
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 5, 2002 at 1:45 am
already tried using sp_resetstatus and dbcc dbrecover() and it still didn't change the db status. anymore ideas?
June 5, 2002 at 1:45 am
already tried using sp_resetstatus and dbcc dbrecover() and it still didn't change the db status. anymore ideas?
June 5, 2002 at 5:21 am
Shut down your SQL Server so the file will no longer be locked. Then copy the db files to a test copy of SQL 7 and use sp_attach_db to see if it marks suspect again (you may need to do sp_resetstatus as the db is marked internally). If it doesn't then try sp_dteach_db on the old server and sp_attach_db. If still doesn't work detach again and this time copy the unmarked version from the other server back to the prod DB and see if can attach. If the reset status does not work on the test server then you may have a bad DB file and I don't know of any tools offhand to help recover the data, will will need to restore the last good backup.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply