|Ngày gửi: 27/04/2010 lúc 7:40 chiều | Đã khóa IP
How to repair a SQL Server 2005 Suspect database
If your project's database is in suspect mode, then no transaction
will take place until and unless you repair your database. That causes
a show stopper for your up and running application. Here, you will find
a way to get out of this.
Your Database is in Suspect Mode. I guess, you
haven't experienced this problem till now. But, if it comes to you and
if the database is LIVE, then it's time to read this article to get out
of this tension.
Using the Code
If you find your database in Suspect mode, then please keep your
nerve strong. Just proceed step by step what I am written below. I
think you will get out of this trouble. SQL Server 2005 introduced a
new DB Status called Emergency. This mode can change the DB from
Suspect mode to Emergency mode, so that you can retrieve the data in
read only mode. The steps are... After executing the script given
below, you will get back your database in operational mode. Actually I
have tried with two of my existing live systems and found no data loss.
Note: Obviously there are two more options available. Run
ensure the database is returned to a structurally and transitionally
consistent state. Here are a few things to bear in mind about emergency
mode repair: it's a one-way operation. Anything it does cannot be
rolled back or undone. If this worries you (if someone ever got into
that state, then surely don't have the healthy concern about data that
they should have in the first place) then make a copy of the damaged
database before you run emergency mode repair.
As it's a one-way operation, you cannot wrap it in an explicit user-transaction.
It's the only repair option available in emergency mode - if you try to use
REPAIR_REBUILD, then it won't work.
Collapse code snippet
EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
Points of Interest
You will be happy that your database as well as the application are still in workable condition. :)
About the Author
Love to code|
Được sửa bởi DUCVINH83 trên diễn đàn 27/04/2010 lúc 7:46 chiều