May 30, 2011 at 4:49 am
Please, sometimes i have some databases in sql server 2000 which are marked "SUSPECT". Each time, i always have to restore the database and lost data. Can somebody tell me whether there is another way to solve this kind of problem?
Thanks!
May 30, 2011 at 4:53 am
abdielmomo (5/30/2011)
Please, sometimes i have some databases in sql server 2000 which are marked "SUSPECT". Each time, i always have to restore the database and lost data. Can somebody tell me whether there is another way to solve this kind of problem?Thanks!
Is this test question or real time issue?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
May 30, 2011 at 4:56 am
Read the gail's post
http://qa.sqlservercentral.com/articles/Corruption/65804/
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
May 30, 2011 at 5:07 am
Restoring from backup is the recommended way to resolve a suspect database. If the backup strategy was correct, the DB can be recovered right up to the point of failure.
It's only if there are no backups that one would repair the DB, because that will result in data loss in most cases.
If you often have DBs going suspect, do some investigation of the IO subsystem, corruption is typically a hardware error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2011 at 7:51 am
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 REPAIR_ALLOW_DATA_LOSS to 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.
EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
May 30, 2011 at 7:59 am
SUPER SQL STAR (5/30/2011)
EXEC sp_resetstatus 'yourDBname';ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
No, no, NO!
That is the absolute LAST resort for fixing suspect databases. It is NOT the default action, it is not the first thing that should be tried. It is literally the very last thing that you do if you have no backups and no other options.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2011 at 8:12 am
GilaMonster (5/30/2011)
SUPER SQL STAR (5/30/2011)
EXEC sp_resetstatus 'yourDBname';ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
No, no, NO!
That is the absolute LAST resort for fixing suspect databases. It is NOT the default action, it is not the first thing that should be tried. It is literally the very last thing that you do if you have no backups and no other options.
Seriously I'd watch myself SSS, she's like a quadruple black belt or something... don't anger her :hehe:.
May 30, 2011 at 8:16 am
SUPER SQL STAR (5/30/2011)
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 REPAIR_ALLOW_DATA_LOSS to 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.
EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
I very much agree with Gail. Of course, I can't refute what she says 🙂
Just think of one thing, what are backups for if we straight away do emergency repair?
We could as well disable the maintenance jobs and ignore database backups once and for all 🙂
M&M
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply