Error 823- Resolve Efficiently Error code 823 of MS SQL Server Application

The error 823 is mainly related because of hardware or driver malfunction, and because of this you might see this error type in your SQL server Database.

" 2003-07-28 09:01:27.38 spid75 Error: 823, Severity: 24, 
State: 2
2003-07-28 09:01:27.38 spid75 I/O error 1117 (The request could not be performed because of an I/O device error.) detected during read at offset 0x0000002d460000 in file 'e:\program files\Microsoft SQL Server\mssql\data\mydb.MDF' "

There is a nice tutorial written in Squidoo, where someone who is also using SQL server faced this kind of situation. Check out his blog for SQL data recovery and you might be able to know and rectify in much better way and resolve your error 823 in much easier way

SQL Data Recovery- How to Recover and Repair the Microsoft SQL Server Database

This small tutorial will help you understand how to verify and repair SQL Server Files to keep your precious database intact. After grasping the key point the user will be able to manually check the integrity of their database.

Assumption made:-

1. You have a MS SQL Server running.

2. Have complete administrative privilege.

To check if MDF File is Corrupt or not ?

Create an engine object by using the below mentioned query

• SqlCeEngine engine = new SqlCeEngine("Data Source = AdWks.sdf");

Where AdWks.sdf is your database path.

Call the “Verify” method to check the database integrity.

• if (false == engine.Verify()) {...}

The purpose of the verify method is to check whether the provided data is corrupted or not. If after applying the above query you get the following result you will be able to analyze its integrity.

1. True

The database need not any modification and is in a healthy state

2. False

False Implies that the database is corrupt and Needs to be repaired first for normal use.

Alternate Way to Check the Data Integrity

There is an alternate method to check the integrity of your database and should be done on regular basics to check the consistency and integrity of the database. SQL server provides some other queries for the purpose of data retrieval. There are two type of inconsistency logical and Physical, In case of former you don't have to worry much and the inconsistency in the database is a result of wrong use of commands and keywords. For example you may have provided a wrong foreign key which results in inconsistent output, so such type of logical inconsistency is easily managed. The main problem arises when there is Physical error in the database which generally is a result of faulty Hardware or outdated drives. So you should give your first priority in updating these two before performing any restoration in corrupt database. You can check the logical and physical integrity of the entire component by performing or running some basic commands. These commands will help you access the severity of your database, so that you can appropriate steps to bring it back to normal.

The following commands are illustrated with their working mechanism:-

1. The first command given in the picture help you to access the integrity of the database and perform all integrity check, "WITH NO_INFOMSGS" you can tell the application not to generate informational messages.

2. The second command checks the common hardware failure and if found in your system will help you take preemptive measure to tackle it. The command also checks if there are any checksum failure or any inconsistency in the pages.

You should make it a habit to perform these checking on regular basis so that you will be better prepared if you encountered with it.

Recovery Measures and Methods

If the application provides you with a True output then hurrah! You do not need to make any changes in your database and they are perfectly healthy but if however the result is false then you need to apply further programming to get them back to normal.

Recover the Corrupted SQL File

 You need to call the Repair method to fix the SQL Database. You can choose from the following different Repair methods based on the criticality of the database.

RepairOption.RecoverAllOrFail

In this method either all or none of the database will be recovered. This is the most effective way to recover and protect the database.

RepairOption.RecoverAllPossibleRows

In this the method tries to read as much data as possible and will try to restore even the corrupt database, but the major drawback is that the recovered database might not be fully corruption free.

RepairOption.DeleteCorruptedRows

 As the name suggests in this method the method will discard all the corrupt rows, if this method is invoked be prepared for a significant data loss.

RepairOption.RecoverCorruptedRows

 In this the method tries to recover as much of data corrupt data as possible, but one needs to understand that even if it is able to recover its element, there is no guarantee that the recovered data is corruption free.   

One Demo Example in this regard is (programmed in VB Script).

If everything is performed as mentioned the user may not face any difficulties in analyzing and performing the MS SQL data recovery. If the database is highly critical and needs every element to be recovered then you should try a SQL Data Recovery Software for 100% data recovery.

Backup MS SQL Server Database and Fix SQL Server File Easily

In today’s competitive market Business continuity is a crucial component for success. You can’t afford to sit idle because of your database crash and as with any good DBA you need to create a systematic backup plan for database crisis situation to minimize the downtime. Here is the step by step guide to create a backup of MS SQL Database so that you can easily bring your database back to online during the system crash.

 Step by Step guide:-

1. Expand the Management Studio.
2. Right click “Backup Devices” and choose “New Backup Devices”.
3. Specify the backup method from “File” or “File”

The point to note here is that “Tape” method can only be activated if the tape device exists on the local computer, so if you don’t have the tape devices then choose the “File Method” option., ones you specified these parameters you will be able to take the backup of your database. Follow these steps:-

1. Unfold the SQL Server and then choose “database”.
2. Point to “Task” and choose “Back-up”.

The Back-up will come up and in that from the General Tab you have to make a choice of the backup type.

1. Full Backup.
2. Differential Backup, or
3. Transaction Log.

For the sake of simplicity in the “New Media set name” and “new media set description” write something about the backup which help you or anyone else to understand about this particular backup. You can also specify when this backup is going to expire.

Now perform the following in the “Destination” section:-

1. Choose the “disk” radio button or “Tape” button if you have connected the tape device with the system.
2. Specify the destination path of the backup with the help of “Add” button.
3. Click “Ok”.

After you have selected all the above option the specified database or Transaction log is then backed up. If you want to know the name, its physical location or the type of backup device on which you have done the backup can be checked by expanding the Management and Backup Device folder. After this procedure you will be able to use the backup anytime you wish, and sometime in rare cases if that backup file gets corrupt then download this tool to repair MS SQL backup file.