June 26, 2008 at 3:23 am
only provided you really did manage to resolve all corruption (via DBCC or any other mean), then only you can reset the suspect status via sp_resetstatus (dbname). SQL 2005 apparently doesnt allow you to change any of the system table directly anymore to reset a database status even with the "allow updates" set to 1 in sp_configure. This hack only available to sql 2000.
other means would be restore for a good known backup (MOST PREFERRED METHOD) or creating another DB and transferring all records from the suspect DB to this empty DB, via bcp or other means after setting your DB status to emergency mode. normally via bcp, the download will stop when it hits the corrupted row. so, you'll still be able to get data out, but its a matter of how much data can you salvage.
Simon Liew
Microsoft Certified Master: SQL Server 2008
June 26, 2008 at 4:24 am
http://dhaneenja.spaces.live.com/blog/cns!52063E89E23A5AAB!390.entry
Tharindu Dhaneenja.
MCTS,MCITP(SQL Server),OCA(Oracle)
http://www.databaseusergroup.com
June 26, 2008 at 6:53 am
Tharindu, just a note. 'allow updates' is a hack only for sql 2000, and apprently they discontinue this hack for sql 2005. so far, there's no info from the question originator on what sql version he's on. hence, its really hard to go to the next step.
Simon Liew
Microsoft Certified Master: SQL Server 2008
June 26, 2008 at 7:48 am
The problem is with SQL Server 2005 and 2000...
June 26, 2008 at 8:59 am
ALTER DATABASE [DBNAME]
set ONLINE
this will get the database off emerency mode.
Maninder
www.dbanation.com
June 26, 2008 at 9:21 am
If Mani's suggestion doesn't seem to work, stop and restart the SQL service, then if needed run the command again.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply