A database can get corrupt due to various reasons such as system failure, bad programming code and unintentional deletion. Database corruption can affect all types of databases like Access, SQL Server, Oracle and MySQL. This article introduces two ways (both free) that you could use to recover data from a corrupted database without losing any data. You do not need to be an expert in sql or software programming language either! If you know how to copy and paste files and navigate within windows explorer, give these methods a try! I hope the contents of this article will help your situation when dealing with a corrupted database.
2 Ways to Recover Data from Corrupt Database
Method 1
This method uses Microsoft SQL Server Management Studio Express to recover data from a corrupted database file. This technique works with all types of files including Access, Excel, .mdb, .mdf and .ndf.
Open up Windows Explorer. Navigate to the location where your corrupt database file is residing. For example my ‘C:\’ drive holds all my user databases. Right click on the file that you are trying to recover data from and select “Open with” option as shown below:
Right click on the Database > Open with… > Select “More..” > Click on All Programs under “Available Program”. Scroll down until you find the program called “Microsoft SQL Server 2008 R2 Management Studio Express”. Once selected it will ask you to confirm the action and open with Microsoft SQL Server Management Studio Express.
After opening the program, click on the “Attach” database option from the toolbar as highlighted in red below:
In the dialogue box that pops up, leave all default settings and follow through. This will attach your corrupt database file into a new window that looks exactly like it used to look before it became corrupted! You can now copy data from this window into another location for backup/archival purposes. Do not save back over top of your original file though because you may end up recreating a further corruption problem next time you try to access the database again!
Method 2
The second method uses a sql Query Analyzer tool called “SqlWright”. This method is best used when you are trying to recover deleted records from a corrupted table. Open up your corrupt .sql file with SqlWright by clicking on “File” > “Open” > Navigate to the location of the file that needs to be recovered and open it by double clicking on it. If you have multiple tables in your sql script, I would recommend only recovering the one table at a time because sometimes if you attempt to recover more than one table at a time, you may end up writing over top of another good working table’s data!
If you do not know how many tables or records will be affected with this corruption issue, then use ‘SELECT COUNT (*) FROM table name’ (where table name is an actual table name) to find out how many records are currently in the table. If you are not sure which table you are trying to recover data from, use ‘USE database name’ first before running any sql queries. This will set the default database for future queries.
You will now see a window with all your SQL code and it probably looks like something below:
Run the following query: “SELECT * FROM table name WHERE deleted=0” (where table name is an actual table name). ALL deleted records will appear listed at the bottom of this new query! Copy and paste these into a text editor such as notepad or Microsoft Word and save it as a .sql file so that they can be imported back into the database when it is recovered.
To recover this data, runs the following query: “SELECT * FROM table name” (where table name is an actual table name). Do not modify any other settings in the file except for changing the highlighted red text below to your correct server login credentials and database location. In order for this to work for you, you have to have administrative rights on the SQL Server where it is being recovered from!
Conclusion:
Using SqlWright is a quick and easy way to get your data back from a corrupt or inaccessible database. Keep in mind that it cannot repair the existing corruption, but only get you the data that was stored within it!
