Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2015
    Posts
    1

    Unanswered: How to Repair Damaged SQL Server Database?

    Due to an unexpected reason my SQL server database has been corrupted and now I immediately want to repair it but don't know how to do this.

    please someone provide me any solution or application.

  2. #2
    Join Date
    Aug 2008
    Posts
    147
    Are you able to post some error messages?
    Depending on the level of corruption and type , there might be information in the msdb database - read this post http://www.sqlserver-dba.com/2014/09...ct_pages-.html
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  3. #3
    Join Date
    Feb 2015
    Posts
    1

    re: How to Repair Damaged SQL Server Database?

    Quote Originally Posted by AethelwealdTurner View Post
    Due to an unexpected reason my SQL server database has been corrupted and now I immediately want to repair it but don't know how to do this.

    please someone provide me any solution or application.
    Hi,
    NOTE: Before doing the following work around first take (DATA) .mdf, (LOG) .log files in the DATA folder of MSSQL into some other drive....
    Once upon a time I too got same issue... I found that my master database is corrupted...
    The following is the work around which is worked for me.....

    1) First of all, start MSSQL as console (-c) in single user mode(-m)
    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn>sqlservr. exe -c -m -sMSSQLSERVER -T3608

    NOTE:
    3608 --> Starts SQL and recovers only the master database.

    Now master database is recovered..

    2) Secondly i got this error: Unable to open the physical file
    "e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\ objfre\i386\model.mdf". Operating system e
    rror 3: "3(The system cannot find the path specified.)".
    Error: 17204, FCB::Open failed: Could not open file e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\o bjfre\i386\model.mdf for file number 1. OS error: 3(The system cannot find the path specified.).

    i figured out that e:\ is not at all available in my system.

    Fix for above error is as follows:
    ************************

    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn>NET START MSSQLSERVER /f /T3608
    The SQL Server (MSSQLSERVER) service is starting.
    The SQL Server (MSSQLSERVER) service was started successfully.

    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn>SQLCMD -sMSSQLSERVER
    1> SELECT name, physical_name, state_desc FROM sys.master_files ORDER BY database_id;
    2> go
    Also try to recover it with MDF Open File Tool http://www.mdf.openfiletool.com/
    Now notice those wrong file names; and run following commands ...

    Note: you need to change the file name location (where the original .mdf, .ldf files are available in ur system. In my point of view 'c:\model.mdf' and soon) ..

    1>ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = 'c:\model.mdf');
    2>ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = 'c:\modellog.ldf');
    3> go

    1>ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData, FILENAME = 'c:\MSDBData.mdf');
    2>ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog, FILENAME = 'c:\MSDBLog.ldf');
    3>go

    1>ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'c:\temp.mdf');
    2>ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = 'c:\temp.ldf');
    3>go

    1> SELECT name, physical_name, state_desc FROM sys.master_files ORDER BY database_id;
    2> go

    Now you will get modified file paths where you have respective file paths. Changing the paths as described above worked, but still I was not able to log in.
    --To list the users
    1> select loginname from master..syslogins;
    2>go
    My windows account was mising, so I have added it:>

    1> CREATE LOGIN [COMPUTERNAME\USERNAME] FROM WINDOWS;
    Then I was able to login with Management Studio with Windows Authentication.

    --Give sysadmin rights to ur login acccount
    1> EXEC sp_addsrvrolemember 'domainName\WindowsUserName', 'sysadmin';
    2> go
    1> SELECT loginname, sysadmin FROM master..syslogins;
    2> go
    You can see the login users list
    1> exit

    C:\Program Files\Microsoft SQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\Binn>NET STOP MSSQLSERVER
    The SQL Server (MSSQLSERVER) service is stopping.
    The SQL Server (MSSQLSERVER) service was stopped successfully.

    Then i enabled service broker for msdb database.

    Finally i attached all user databases to my instance...

  4. #4
    Join Date
    Jun 2011
    Posts
    31
    Provided Answers: 1
    Please post the complete error message. Without error message, it is very hard to suggest specific solution.

    Run this command and share the error message;

    DBCC CHECKDB database_name With No_INFOMSGS, ALL_ERRORMSGS

    Depending upon the error message, DBCC CHECKDB command itself suggests a proper repair options: repair_fast, repair_rebuit, repair_allow_data_loss to to fix the error.

    Note: Remember that DBCC CHECKDB is a powerful tool, but it can be dangerous because you may lose some data from the database so always use it as a last resort.

    If you can afford some money then try Stellar Phoenix SQL database repair software as it efficiently repairs and recovers data from corrupt mdf file without any modification in the original database.

    Before doing any repair operation, make a backup of your database.

    Good Luck!!

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    1. Will the SQL Server start?
    2. What makes you think that the database is corrupt?
    3. Do you have a usable, reasonably current backup of the database?
    4. What is the value (does this warrant calling a professional?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2015
    Posts
    1

    re: How to Repair Damaged SQL Server Database?

    Also you can read here about SQL server database corruption http://goo.gl/b8xQTR Once I've had one corrupted MDF file. I have recovered it with MDF Open File Tool . I hope that you'll not need any special soft for your database.

  7. #7
    Join Date
    Feb 2015
    Posts
    2

    Thumbs up Helpfull Way to Recover SQL Database

    I’d try the procedure described below or third party tool such SQL Recovery Tool if that doesn’t work and database is severely corrupted.
    There might be other options out there. This is something that worked for me once but there are no guarantees that it will work at all times.
    Stop SQL Server instance -> Copy MDF and LDF files to another location -> Delete original MDF and LDF files -> Start SQL Server instance again -> Create new database with exact same name and file names -> Stop SQL Server -> overwrite newly created MDF and LDF.
    After this your database should be back online. If it is then go ahead and put it into EMERGANCY mode and SINGLE USER mode.
    Finally go ahead and execute DBCC CHECKDB like this
    DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
    If you can get to this and execute last command successfully you should be good. If not then your only options are restoring from backup and/or using 3rd party tools.

  8. #8
    Join Date
    Feb 2015
    Posts
    2

    Thumbs up Some helpfull way to recover SQL database

    Hi

    I have tried such procedures but still fails to recover corrupt SQL database.Then only options are restoring from backup and/or using 3rd party tools.
    must go to
    https://www.google.co.in/?gfe_rd=cr&ei=fRvsVJ2IEOHA8gfqiICQBw&gws_rd=ssl#q= sql+recovery+tool


  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    And that is why backups are so valuable.

  10. #10
    Join Date
    Nov 2015
    Posts
    7
    Quote Originally Posted by corncrowe View Post
    And that is why backups are so valuable.
    Very true lines. Be it SQL database or whatever, we must take backup of our invaluable data and update it regularly. Corruption and accidental deletion are two most common reasons for data loss which are inevitable. So be smart and take backup of almost everything.

    Thanks.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •