Wednesday, June 7, 2017

How to recover a corrupt SQL Server table if I do not have a backup?


Introduction

It is very common to lose some data and have a table or a database corrupted because of hardware problems, un unexpected server reboot, a virus, a worm, malware.

In my case, I had a table corrupt.

This time we will show how to recover data from a corrupted database. If we do not have a backup, it is sometimes hard to recover the data. We will show a simple way to do it, with a special software.

Getting started

I just run the following T-SQL sentence:

SELECT TOP 1000 [CustomerID]
      ,[NameStyle]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[Suffix]
      ,[CompanyName]
      ,[SalesPerson]
      ,[EmailAddress]
      ,[Phone]
      ,[PasswordHash]
      ,[PasswordSalt]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2012].[SalesLT].[Customer]

The error message received was the following:
Msg 824, Level 24, State 2, Line 2

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xd3e289a2; actual: 0x90e7d9a6). It occurred during a read of page (1:469) in database ID 5 at offset 0x000000003aa000 in file 'C:\sql\AdventureWorksLT2012_Data.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

My table was corrupted and I need to repair it.
This is because some sectors have errors. If we do not have a backup, is it is possible to repair the table?

This time we will show a nice and very intuitive tool to do it.

Stellar Phoenix SQL Database Repair


This software is a simple tool to Repair your database. You can download the software using the following link:


This software allows you to:

  • Repair corrupted data files.
  • You can also recover tables, views, keys, indexes, rules and defaults with this tool.
  • Recover deleted records.
  • You can search and recover data objects.
  • It supports SQL 2016 and lower versions.

Let's take a look to the tool.

First install it and then open it. This software has a 5 MB installer. It takes few minutes to have it ready.

Once installed, press the Select Database icon:




You will need to find the mdf file corrupted. In the error message in red displayed before, SQL Server told me that the file corrupted was this one:

C:\sql\AdventureWorksLT2012_Data.mdf

This software repairs mdf and ndf files. The mdf files and ndf files are the primary and secondary files where the database information is stored. Sometimes the databases have just the primary data file, but if it is a big database, it may have several data files to store the information. Select your data file:




 Once selected the data file, press the repair button:


The program will ask the SQL Server Version. As you can see it supports from SQL Server 7.0 (released in 1998) until SQL Server 2016!

You may need to register the software to save your repaired database:


If the database is repaired successfully, you will be able to run the query:


Conclusion
As you can see, it is very simple to work with Stellar Phoenix SQL Database Repair. You just need to select the files to repair and that is all, you will have your database back again.

This tool is simple to use, it takes few minutes to install, run and learn. I strongly recommend this software.

0 comentarios:

Post a Comment