Tuesday, October 2, 2007

MS SQL server: database marked as "suspect"

Q: I've a database which was marked in "Recovery" and now "suspect"in SQL Server Enterprise.
I've restarted the server but still came up as "supspect".
I don't know what to do. Is there something I can try out before restoring the database from a backup?
I'm using SQL Server 2000.

A:
1. Detach a suspected db. Move the .mdf and .ldf files to the another location.

2. Create a new db with a same name and a same files .mdf and .ldf (name and location).

3. Stop MS SQL server.

4. Overwrite the new .mdf file with an old one.

5. Start MS SQL server.

6. Open QA and run the script:
-------------------------------
use master
go
sp_configure 'allow updates', 1
reconfigure with override
go
-------------------------------

7. From the QA run the script:
-------------------------------
select status from sysdatabases where name = ''
-------------------------------
Remember the status!

8. Run next script:
-------------------------------
update sysdatabases set status = 32768 where name = ''
-------------------------------
9. Restart MS SQL server

10. Now you should see your database in the Emergency mode.
You may script all objects at this point.

11. Run the next script:
-------------------------------
DBCC REBUILD_LOG('', 'log file name with a full path>').
-------------------------------
You should see the following message:
Warning: The log for database '' has been rebuilt.

12. If no errors, run the next script:
-------------------------------
use ''
go
sp_dboption '', 'single_user', 'true'
go
DBCC CHECKDB('', REPAIR_ALLOW_DATA_LOSS)
go
-------------------------------

13. If no errors, run the next script:
-------------------------------
sp_dboption '', 'single_user', 'false'
go
use master
go
sp_configure 'allow updates', 0
go
-------------------------------

14. If any errors occur, reset the status of your db to the status at step 7.

No comments: