Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 How to Repair Damaged SQL Server Database?

Author  Topic 

shivenrosi
Starting Member

1 Post
Posted - 2013-09-02 : 07:23:55
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.

bandi
Master Smack Fu Yak Hacker

2242 Posts
Posted - 2013-09-02 : 08:21:25
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
Go to Top of Page

rodyroon
Starting Member

7 Posts
Posted - 2013-09-03 : 03:33:11
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

1 Post
Posted - 2013-09-23 : 07:05:13
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 - 2014-04-22 : 00:47:41
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 - 2014-04-25 : 08:17:58
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.
Go to Top of Page

tommystewson
Starting Member

1 Post
Posted - 2014-04-28 : 21:18:35
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 Post
Posted - 2014-05-05 : 07:32:30
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
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site