#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2015
    Posts
    1
    Rep Power
    0

    Corrupted sql server 2012 database


    I have a 240Gb database that mostly stored his data in 1 table. This table is corrupted. When I run a dbcc chekdb (with or withour repair) I have theses type of error messages

    Repairing this error requires other errors to be corrected first.
    Msg 8939, Level 16, State 98, Line 1
    Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -1390612485740232704 (type Unknown), page (36354:-1442804222). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12584969 and -14.
    Repairing this error requires other errors to be corrected first.
    Msg 8909, Level 16, State 1, Line 1
    Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -1152921504606846976 (type Unknown), page ID (1:6769763) contains

    EVENT ID 827 in eventvwr

    The table contain around 900 millions rows. My last good backup that I have restored is couple of months old. I was able to sync back all the tables except this one.

    I have tried to manually export the missing data from the corruped table to the other one without success...

    I am looking for any ideas or suggestion that will help me putting back this db up and running with the data transfered.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2015
    Posts
    1
    Rep Power
    0
    In MS SQL Studio run new query or next variant at hard corruption of sql database is https://repairsql.recoverytoolbox.com/ Recovery Toolbox for SQL Server

    1. dbcc checkdb(‘DB-NAME’) with no_infomsgs
    DB-NAME is a name of your corrupted database. If this is completed without any errors then the database does not need to be repaired.
    2. Alter database DB-NAME set SINGLE_USER
    Before we begin repairing the database, the database must be set in single user mode.
    3. dbcc checkdb(‘DB-NAME’,REPAIR_REBUILD)
    There are number of repair model usually we use first REPAIR_REBUILD. When everything is ok go to step 5.e (multi user mode) If not, go to next step.
    4. dbcc checkdb(‘DB-NAME’,REPAIR_ALLOW_DATA_LOSS)
    This command may cause data loss. When everything is ok go to step 5.e (multi user mode) If not, go to next step.
    5.
    a. ALTER DATABASE DB-NAME SET EMERGENCY
    b. ALTER DATABASE DB-NAME SET SINGLE_USER
    c. DBCC CHECKDB (DB-NAME, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS
    d. ALTER DATABASE DB-NAME SET ONLINE
    e. ALTER database DB-NAME set MULTI_USER
    Description:
    a. Set database to emergency mode
    b. Set database to single user mode
    c. Check database and repair with allow data loss
    d. Back database to online mode from emergency mode
    e. Set database to multi user mode for normal use
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Posts
    1
    Rep Power
    0
    Manually you may try repairing SQL database. I would first try this method:

    1. Copy the database files to a safe location
    2. Rename the existing files (not the ones copied to a safe location) to something slightly different. For example, if the data file is named MyDB.mdf, rename it to MyDB_orig.mdf.
    3. Create a new database on the SQL instance using the same database name, the exact file names and paths of the original files, with the files the exact size the original files are.
    4. Shut down the SQL instance
    5. Delete the new database files created when you created the new database.
    6. Rename the original files back to their original names (so the new database thinks these files are its files)
    7. Restart the SQL instance
    8. Check the database to see if it is online and working
    9. If it is, BACK UP the database
    10. Run DBCC CHECKDB on the database to verify there is no corruption
    11. Now set up maintenance routines to back up the database on a regular schedule.


    you may also try SQL Database Recovery Tool if manual repair method fails.

IMN logo majestic logo threadwatch logo seochat tools logo