How to recover suspect database

  • Hi,

     

    I have SQL 2005 database in suspect state. By my opinion it is damaged because of hard disk error. I’m  trying to set the database in such kind of state so, that I can read undamaged data from it.

     

    First I was tried with EXEC sp_resetstatus 'PIS';

    and I was got the following message:

    The suspect flag on the database "PIS" is already reset.

     

    Then I was tried to use DBCC checkdb('PIS')

    but I was got the message:

    Msg 926, Level 14, State 1, Line 1

    Database 'PIS' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

     

    Then I was tried to set database in emergency mode with Alter Database PIS Set Emergency

    but I was got the following error again :

    Msg 823, Level 24, State 2, Line 1

    The operating system returned error 38(Dosežen je bil konec datoteke (EOF).) to SQL Server during a read at offset 0x0000000c0b8000 in file 'c:\DATA\DATA\MITIC1.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

    The database is now not suspected but in the Management Studio I see just database without any database objects.

     

    Likewise I was tried to move database to another SQL server so, that I was restore database from older backup on the another SQL server. Then I was stopped this server and original server then I was copied original files (I have 3 files and 1 log file) over new restored files and then I was started new SQL server. The database becomes suspected with all above problems.

     

    Does anybody has ideas or tricks how to correct this database so, that I can read good data from it?

     

    Regards

  • Hi

    Not sure I understand your last paragraph:

    "I was tried to move database to another SQL server so, that I was restore database from older backup on the another SQL server"

    I read this to mean that you have attempted to copy your database to a new server as a preliminary to restoring an old backup.

    You can restore backups to a new SQL server without moving the (probably) corrupt database to that server. Copy the backup file to the new server then restore. Or restore the backup to a new database name on your current server.

    Apologies if I mis-understood...

     

  • Yes. I wasn’t enough clear in the last paragraph. The corrupted database was on the laptop. Just with such kind of trick, I can moved it to my stationary PC. Now I have suspected database in the PC and I want to get good data from it, if at all is possible.

  • Well, you have at least one corrupt page in the database that's being hit during recovery. This means you can't access the database unless you put it in emergency mode (using ALTER DATABASE PIS SET EMERGENCY). You can then poke about an extract data BUT it will be transactionally inconsistent as recovery has not been run. If you don't have any valid backups, or they're too old, the only way to fixup the database is to use emergency mode repair. See my old blog post at http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx - I'm going to update it with more instructions on my new blog.

    Thanks

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Thank you for suggestion. But I have no luck. When I try to put database in emergency mode, I get the error as I was wrote  in the first message. If then I try to run

    dbcc checkdb ('pis', REPAIR_ALLOW_DATA_LOSS)

     

    I get the error:

    Msg 922, Level 14, State 1, Line 1

    Database 'PIS' is being recovered. Waiting until recovery is finished.

     

    Regards

  • Can you do the following and let me know the output you get?

    ALTER DATABASE PIS SET ONLINE -- this should try to run recovery, fail and put the database into SUSPECT or RECOVERY_PENDING

    GO

    ALTER DATABASE PIS SET EMERGENCY -- this should work after the step above

    GO

    You'll then need to put the database into SINGLE_USER mode as well before running repair. If this stuff doesn't work, can you post the results from:

    SELECT state_desc FROM sys.databases WHERE name='PIS'

    Btw - if this is a time-critical problem, I advise you to contact Product Support to help you immediately rather than waiting for forum responses.

    Thanks

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Hello Guys,

    My SQL 2005 MSDB database is put in SUSPECT mode, that has lot number of jobs and Sprocs. I dont have any backup of MSDB.

    I have tried the following Steps/methods to bring it back to Online, but didn't work on this System Database:

    1. Stopped SQL service> copied the MSDB data files into a different drive> restarted Service> Tried SP_Attach_Db to attach these files as a new database. But it failed saying MSDB has been put under Suspect.

    2. Tried Sp_Reset_Status 'MSDB' , though it executed successfully..MSDB didnt turn Online either.(It seems this method is for SQL 2000, Correct me If I am wrong).

    3. Altering the Database to Emergency mode so that I can execute DBCC CHECKDB ('MSDB', REPAIR_ALLOW_DATA_LOSS), then error said that MSDB can't be set to Emergency Mode

    Can some one please tell me, if I can turn my MSDB Online back. I cannot afford losing data by restoring/ re-creating as a new MSDB. I don't have any backup of this MSDB or the data of MSDB as scripts , so if i recreate a new MSDB... there is no way of creating all those jobs/Sprocs from scratch.

    Thanks,

    [font="Comic Sans MS"]Vampire[/font]

    --In 'thoughts'...
    Lonely Rogue

  • Well, first off you probably are going to lose some data from the database being suspect - that's what backups are for. You've probably already realized this, but if you can't afford to lose data from msdb, this should be the wake-up call to start backing up all yoru system databases.

    The procedure you want is described in this blog post: TechEd Demo: Creating, detaching, re-attaching, and fixing a suspect database

    Let us know when you got it working.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • @paul-2,

    Thanks for the reference link. However, will that method work even for System Databases ( in my case its MSDB).

    I am asking you this because as said in the reference, the System DB can't be set into Emergency Mode...then how to proceed?

    Fixing the Database

    If you don't have any backups, then the only way to get into the database is to use EMERGENCY mode. This lets you into the database but you need to be aware that recovery has not completed so the contents of the database are transactionally (and possibly structurally) inconsistent. I'm going to choose to repair the database using emergency-mode repair. See CHECKDB From Every Angle: EMERGENCY mode repair - the very, very last resort for a detailed description of this tool.

    ALTER DATABASE DemoSuspect SET EMERGENCY;

    GO

    ALTER DATABASE DemoSuspect SET SINGLE_USER;

    GO

    DBCC CHECKDB (DemoSuspect, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

    GO

    Thanks,

    [font="Comic Sans MS"]Vampire[/font]

    --In 'thoughts'...
    Lonely Rogue

  • Hmm - that's right - msdb can't be put into emergency mode using the ALTER DATABASE syntax. To get it to work you'll need to update the system tables manually - no easy task, involving booting the server in single-user mode. I haven't tried this on 2005 so I can't say if it'll work or not - maybe I'll play around with it today - but that's the only way you'll be able to get into that msdb database.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Hey Paul,

    I was able to restore my MSDB jobs 😀 without creating a new MSDB.

    Here is what I have done, probably help them lying in the dark struggling with this kind of issue:

    Problem :

    My SQL 2005 MSDB database was put into SUSPECT, bcoz of abrupt server boot, that has many Sprocs and Jobs. I have no backup of MSDB, hence there is no way of restoring the backup to re-create MSDB.

    Solution:

    1. Stop the SQL service > Copy the corrupt MSDB MDF data file (only MDF ) from its location onto a different drive/ location > Start the SQL service > Execute the below SQL query to create a User DB with this available MDF file

    CREATE DATABASE User_MSDB ON

    (NAME=DFT_MSDB , FILENAME='D:\NonSQLDefaultLocation\msdbdata.mdf') FOR ATTACH_REBUILD_LOG

    This will create a User database with all the database files @ 'D:\NonSQLDefaultLocation\'

    2. Now, take a backup of this User_MSDB database

    BACKUP DATABASE User_MSDB TO DISK='D:\NonSQLDefaultLocation\User_MSDB.Bak'

    3. Restore the MSDB database with the backup of User_MSDB , the one we have taken in the 2nd step.

    RESTORE DATABASE MSDB

    FROM DISK='D:\NonSQLDefaultLocation\DFT_MSDB.BAK'

    WITH MOVE'MSDBDATA' TO 'C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSDBDATA.MDF',

    MOVE'MSDBLOG' TO 'C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSDBLOG.LDF',REPLACE,RECOVERY

    Whoa...you are back with your MSDB data.

    --In 'thoughts'...
    Lonely Rogue

  • Hello "INFO01", I was following this post and was curious how things r going with ur corrupted Database. Thanks

  • Neat - but ATTACH_REBUILD_LOG only works if the databases wasn't cleanly shutdown - so your msdb must have been. I wonder why it thought it was suspect then - suspect means that recovery started but couldn't finish? Hmmm. Thanks for following up.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • But, it seems theres been some post-restoration issues of this System Database. After I restored MSDB...one of my job is failing with the below error:

    Message

    [LOG] Step 1 of job 'DataImport' (0x01B59EE6D7841D459A251D07291F784A) cannot be run because the ActiveScripting subsystem failed to load. The job has been suspended

    However, I have found a post corresponding to this issue @ http://blogs.infosupport.com/blogs/bertrand/archive/2007/11/14/Restoring-MSDB-across-instances_2C00_-a-bad-idea.aspx

    Please post if anyone has found the above described issue or something different after restoring MSDB.

    --In 'thoughts'...
    Lonely Rogue

  • Hi,

    I have the similar problem. One of my live databases were suspected and we are using SQL 2008. I have tried to recover the database but when I use the statement "EXEC sp_resetstatus " I got an Warning "You must recover this database prior to access"

    Then when I use "ALTER DATABASE SET EMERGENCY" got error "ALTER DATABASE statement failed"

    Can anyone please help me to recover the suspected database in SQL 2008?

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply