SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 How to Repair Damaged SQL Server Database?
 New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

shivenrosi
Starting Member

1 Posts

Posted - 09/02/2013 :  07:23:55  Show Profile
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.

Edited by - shivenrosi on 09/02/2013 07:27:30

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 09/02/2013 :  08:21:25  Show Profile
Hi Shivenrosi,
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\objfre\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

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...

The supportive URLs are:
http://blogs.msdn.com/b/sqlserverfaq/archive/2011/05/11/inf-hey-my-sql-server-service-is-not-starting-what-do-i-do.aspx

http://social.msdn.microsoft.com/forums/en-US/sqlexpress/thread/54bbcac3-41c5-4a5d-a4f6-2669e538dc82/

--How to create Sql Server login for a domain account?
http://dba.stackexchange.com/questions/15737/how-to-create-sql-server-login-for-a-domain-account

-- Error 18456 causes and solution
http://www.mssqltips.com/sqlservertip/2581/sql-server-error-18456-finding-the-missing-databases/


--
Chandu

Edited by - bandi on 09/02/2013 08:24:39
Go to Top of Page

rodyroon
Starting Member

6 Posts

Posted - 09/03/2013 :  03:33:11  Show Profile
Hello shivenrosi,

It may be possible that your MDF Files of SQL server database have been damaged, If you want to repair it then I would like to advice you an excellent application named as Kernel for SQL recovery Tool. By this efficient and user-friendly tool you can very smoothly repair damaged or corrupted MDF files of SQL Server database including triggers, tables, keys, procedures, indexes etc.
Go to Top of Page

jack adision
Starting Member

Armenia
1 Posts

Posted - 09/23/2013 :  07:05:13  Show Profile
If you are dealing with errors and unable to open your MS Sql database then your MS SQL database is corrupted, and you can’t access any table or data that stored in the database without repairing database. Repairing is also necessary because if you don’t repair it immediately then the database will be corrupted permanently. You can repair your corrupted MS SQL database by using SQL Repair Tool. This tool is very efficient and useful, it is very powerful tool that scan the damaged database effectively and restores almost every objects from the corrupt database such as tables, keys, triggers, views, stored procedures, indexes, deleted records etc. this tool has many features that is very helpful to recover database recovery task such as it repair corrupt SQL database, restore the objects of .mdf and .ndf file, this tool also remove all errors, provides the preview of the recovered database object etc. you can unspammed MS Sql Repair tool to repair your MS Sql database.
Go to Top of Page

elliswhite
Starting Member

36 Posts

Posted - 04/22/2014 :  00:47:41  Show Profile
I think you need to try this
DBCC CHECKDB
( 'database_name'
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
Go to Top of Page

bakk
Starting Member

12 Posts

Posted - 04/25/2014 :  08:17:58  Show Profile
I would like to suggest you an effective manual approach by which you can repair your damaged MDF file. You need to execute DBCC Checkdb statements as a new query.
The statements which you need to execute are given in this post:
unspammed

This post will help you a lot.

Edited by - bakk on 04/25/2014 08:19:59
Go to Top of Page

tommystewson
Starting Member

1 Posts

Posted - 04/28/2014 :  21:18:35  Show Profile
There are a lot of third party SQL Server data recovery programs available in the market to repair and recover lost data from damaged sql database: SQL Server Repair Toolbox is an advance database recovery software that deals with corrupted or damaged files of SQL Server database

unspammed
Go to Top of Page

parksmith
Starting Member

1 Posts

Posted - 05/05/2014 :  07:32:30  Show Profile
Microsoft offers an utility 'DBCC CHECKDB' to check & repair corrupt databases. But sometimes it fails to repair due to level of database corruption. In this situation 3rd party MS satisfied SQL repair product may be helpful. There are several such type of software available in the market that claim they can recover data from corrupt database. You can try below link to get more information about sql repair tool.

unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000