Repair Corrupt SQL Server Database

-->

In this blog post, I will try to explain all possible steps that you will have required to repair your corrupt SQL server database. Let's consider a case where you are working on SQL server database, trying to access some records from a table. During table access process you have got an error message that specifies, you can't access desired table due to database corruption. Corruption in SQL server tables can happen due to several reasons; some most prominent reasons are virus attack, power outage, improper system shutdown, hardware failure etc.

What to Do Now? It is the first question that will come in your mind. I will suggest some tips at this point.

• Don't Panic
• Keep Patience
• Take Backup of your database
• Check hardware configuration

How to Check Error Message?

Run DBCC CHECKDB command on the database, it will write a reported error message in SQL server error log. The reported error message will show you how many consistency errors are found.

For example: You may get below error message in your error log

2010-03-31 22:07:06.34 spid53 DBCC CHECKDB (mydb) executed by MYDOMAIN\theuser found 15 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 00000026:0000089d:0001 and first LSN = 00000026:0000089c:0001. This is an informational message only. No user action is required.

Note: You can run DBCC CHECKDB command on the database any time no matter your database is online or offline but you will get information message only when DBCC CHECKDB will run on online database.

How to Fix Corruption: There are three methods that you can try to repair corrupt table. The methods are....

• Check Hardware Configuration
• Restore from Backup
• Run DBCC CHECK with repair_allow_data_loss

Check Hardware Configuration: First of all check your hardware configuration. If you are not a hardware geek then connect with your system support team and fix the issue.

Restore from Backup: If backup of corrupt table is available then restore from updated backup.

Run DBCC CHECKDB with repair_allow_data_loss: DBCC CHECKDB is a free utility offered by Microsoft to repair & recover SQL server database with minimum data loss.

ALTER Database Employee_Details Set Single_User

GO

DBCC CHECKDB (Employee_Details, REPAIR_ALLOW_DATA_LOSS) With ALL_ERRORMSGS;

GO

ALTER Database Employee_Details set multi_user

GO

Read More

Causes & Resolutions for MS SQL Server Error 5120

-->

Are you getting SQL server error 5120, when you try to attach some database files to Microsoft SQL server? This may happen because you have selected a file that is in use by other SQL server instance or selected a wrong file for the attachment. To fix this, firstly you have to check the location and status of SQL server files that you want to attach.

The default location of data & log files are:

C:Program-files\MSSQL\Data\Database-Name_Data1.mdf

C:Program-files\MSSQL\Data\Database-Name_Log1.ldf

Note: Your data & log files may have different location. You can change it according to your need.

The error message that you may got is look like:

Unable to open the physical file "C:Program-files\MSSQL\Data\Database-Name_Data1.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

Possible Cause: As I have mentioned, this error message occurs due to many reasons but it may also occurs when you are not allowed to access the file directories. For the security purpose, database administrator does not allowed you to access the file directories. To access the file, you have to login as administrator or get permission to access the database files.

Resolution: As we know that solution is not rigid for sql database recovery. It may change according to the scenarios or causes. Here, we will discuss two scenarios and its possible solutions.

Solution 1: If you have found access permission problem with the database files then login as a administrator and fix the problem.

Solution 2: If you have found data file is present in C drive & log file is present in E drive then change the location of data or log and placed data and log files in a same drive. Now attach the database and fix it.

Point to Ponder: Generally, solution 1 is enough to solve Microsoft SQL error message 5129 because most of the database administrator (owner) does not allow every administrator to access the database files.

Read More