[Guide] How to restore a SQL Server database marked as "suspect"

Evo

Active Member
Joined
Nov 21, 2009
Messages
165
Reaction score
35
Здравейте ,
искам да ви покажа нещо което ми се случи за 1ви път след токов удар :).Когато се включи компютъра , нямаше нищо в Database MuOnline и беше маркиран като ( Suspected).
Разрових се из нета и октрих как се оправя

At times a database appears to be marked as "suspect" in the Enterprise Manager. SQL Server marks a database as suspect with it can't access the database. What happens at a low level is that SQL Server sets one of the bits in the status field in the sysdatabases table.
In general, this problem has no trivial solution. A simpler case occurs when the file that hosts the database is renamed from the command prompt or Windows Explorer. In this case, you just have to restore the original file name and then manually restore the bit in the status field with this command:


Step 1
update sysdatabases
set status = status & ~256
where name = 'MuOnline'


Keep in mind that the command above can be successful only after enabling writing to system tables.
When the error isn't so trivial, you should check the error log to determine whether a restore operation is possible. However, when the data in the transaction log has been damaged, the restore procedure can hardly succeed. In this case the best solution is to restore the database from a recent backup. When this isn't possible, you should bring SQL Server in the so-called emergency mode. This state is entered by setting a bit in the status field in the sysdatabases table. The docs in the Books On Line report that you must OR the field contents with the value 32768, but the correct value is -32768. Thus, this is the command you need:



Step 2


update sysdatabases
set status = status | -32768
where name = 'MuOnline'


After this command you must stop and restart the SQL Server service. The Emergency Mode prevents SQL Server from restoring the suspect database, which now appears to be available. If the database is now accessible, you should be able to read data using standard techniques, such as a bulkcopy command or SELECT INTO commands
 

NakazaTeLq

New Member
Joined
Feb 15, 2010
Messages
32
Reaction score
3
не е лошо да го напишеш каде ги пишеш :) иначе добре е
 

Evo

Active Member
Joined
Nov 21, 2009
Messages
165
Reaction score
35
в Query-то ........