MSSQL Database Recovery

SQL Server database storage space is divided into pages, which are numbered sequentially. When you perform any operation, these pages serve as the basic I/O unit. Each of these pages is marked with a corresponding header (96-byte in size) that conveys information like page number, free space available, page type, object ID etc. If you run DBCC CHECKDB on database and it reports errors on database pages, the odds are that the page header is damaged. This leads to data inaccessibility and to restore lost information, you require using your recent data backup. However, if backup is damaged, not updated, or not available, you need to use SQL Recovery tools for safe repair and restoration.

Consider a practical instance, you run DBCC CHECKDB on your SQL Server database and get a series of error messages, similar to:

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID O_ID, index ID I_ID, partition PA_ID, alloc unit ID AU_ID, page P_ID, Test (TEST) failed. Values are VAL1 and VAL2.

Cause

Error 8939 arises due to corruption in page header. One of the possible causes is hardware errors. The test that failed is defined in TEST string, while VAL1 and VAL2 are dependent upon error state.

Solution

To correct the above error, you need to follow these steps:

• You require examining system and application logs for hardware failures. Replace the affected hardware components. Also, you need to ensure that no write caching is enabled on disk controller (this is the issue when data corruption is observed frequently)
• Use your recent data backup to restore lost data.
• In case of valid backup unavailability, run DBCC CHECKDB command to determine corruption state and repair clause to be used. Next, you need to run DBCC CHECKDB with recommended repair clause. Note: This process results into data loss as the affected pages will be deleted.
• For safe SQL Repair, use commercial repair applications. SQL Recovery software are read-only, advanced, and automated software that scan damaged databases and restore them with original content and view.

Stellar Phoenix SQL Recovery is a comprehensive repair tool for damaged SQL Server databases. It supports SQL Server 2000 and 2005. This SQL Repair software is available with self-descriptive interface and restores all database objects (tables, user defined data types and functions, triggers, stored procedures, views, rules etc.). The software is compatible with Windows Vista, XP, 2003, 2000, and NT.

When you use DBCC CHECKDB command on SQL Server database (MDF) file, you may face database inconsistency problems that could cause issues like database corruption. In case of corruption, SQL Server can not recognize the MDF file and thus data stored in it becomes inaccessible. To resolve MDF corruption issues, you need to repair and restore the MDF file using SQL recovery software.

The DBCC CHECKDB command might display error messages due to database inconsistency in your SQL Server database. It takes place when the underwritten conditions are true:

1. MS SQL Server loads data to the column which has a sql_variant data type.
2. The data gets created in another column which has a sql_variant data type and the data is sent on-the-wire by the SQL Server.
3. Data is sent either to an intermediate file or directly to another database server based on Microsoft SQL Server.
4. sql_variant column holds character values.

In case of database inconsistency, SQL Server may not work properly and data stored in the MDF file can not be accessed. Here are some examples of the SQL statements which could result into such behavior:

1. A BULK INSERT or BCP statement from the file which was originally created using SELECT statement or BCP IN command from the SQL Server.
2. Data load which is using the Data Transforming Services.
3. Data load which is using the SQL Server DMO Transfer Object.

In case of MDF file corruption, you may encounter the below given error message:

“Msg 2537, Level 16, State 43, Server MYSERVER, Line 1
Table error: Object ID 1977058079, index ID 0, page (1:75), row 0. Record
check (Valid SqlVariant) failed. Values are 4 and 0.
DBCC results for ‘MyTable’.
There are 0 rows in 1 pages for object ‘MyTable’.
CHECKDB found 0 allocation errors and 1 consistency errors in table.

In case of corruption, MDF file repair is required to access data from the database. It is best possible using third party SQL Repair software. These software incorporate efficient scanning mechanisms to scan the damaged MDF file and extract all the data from it.

SQL Recovery software are result-oriented and easy-to-use tools that do not require sound or prior technical skills to achieve recovery of your precious data.

Stellar Phoenix SQL Recovery is the most advanced and powerful SQL repair application that ensures complete repair of your corrupt database (MDF) file. It supports Microsoft SQL Server 2005 and 2000. The software is compatible with Microsoft Windows Vista, XP, 2003, 2000 and NT.

The rows of a SQL Server table are divided into partitions that are numbered from 1 to n. By default all rows are consisted in a single partition, however one can also define the size of partition. All rows of partition are stored as a B-tree or heap structure. The actual data is stored in leaf nodes of leaves (called text data node), while rest other nodes (called text index nodes) are used to store index values for leaf node. But sometimes, you observe table inconsistencies because of mismatch between one or more child nodes and their corresponding parent nodes. As a result, you receive table corruption errors. Such situations need to be resolved using DBCC CHECKDB command, backup or commercial SQL Recovery utilities.

You might encounter the following error with your SQL Server database table:

Server: Msg 8961 , Level 23, State

Table error: Object ID O_ID. The text, ntext, or image node at page P_ID1, slot S_ID1, text ID TEXT_ID does not match its reference from page P_ID2, slot S_ID2.

Where, ’state_number’ might be 1, 2 or 4.

Cause

SQL Server displays error 8961 when it finds mismatch between child node and parent node and corruption in a text object. Specifically, you might infer the exact cause of error message by the ’state_number’ it is displaying:

State 1: It occurs when timestamps in node and parent don’t match with each other.

State 2: The child node is text data node and has greater size as what is supposed by its parent. It might also occur if the child node is text index node and has different size as what is supposed by its parent.

State 4: The child node is actually a text index node, but its parent supposes it to be a text data node or vice-versa. This might also occur due if the level of child node is different from what is supposed by its parent.

Solution

In order to solve such problems, you should follow these steps:

1. Check your system for possible hardware problems

2. Restore database from clean backup, if available

3. Execute DBCC CHECKDB command without using repair clause if no valid backup is available. Again run DBCC CHECKDB but with the repair clause suggested by the previous check

4. Running DBCC CHECKDB causes data loss. Thus, for safe SQL Repair, use third-party database repair applications. These SQL database recovery tools are built with powerful technology to safely scan and repair a damaged SQL Server database.

Stellar Phoenix SQL Recovery is a premier tool to repair damaged SQL databases. It supports SQL Server 2008, 2005 and 2000. With self-descriptive and exceptionally realistic interface, advanced repair options and read-only design, it is an effective SQL Repair tool to repair all database objects. The software is compatible with Windows Vista, XP, 2003, 2000 and NT.

SQL Server includes a database check utility called DBCC CHECKDB. In SQL Server 2005, you can use this command with DATA_PURITY option enabled that allows you to perform data purity validations on each column value in all rows of a table or multiple tables from the database. If you run this check and it outputs error messages that indicates problems with data and hence database integrity. In such situations, you need to use your recent backup to restore the lost information. But, if you find it invalid or inappropriate to deliver required results, SQL recovery tools can be used as a solution.

Consider a scenario, you check your database integrity using DBCC CHECKDB with DATA_PURITY option enabled and it gives the below error message:

“Msg 2570, Level 16, State 2, Line 1

Page %S_PGID, slot %d in Object %d Index %d Column %.*ls value %.*ls is out of range for data type “%.*ls”. Update column to a legal value.”

This error message occurs for every row that contains an invalid column value.

Cause

The above error occurs due to invalid data in a column of a table as invalid floating point values as SNAN, QNAN, NINF, ND, PD, PINF etc. It can also happen due to out-of-range data or hardware issues.

Solution

To correct the above issue, you need to follow any of the below methods:

• You should find the affected rows and manually update them with correct values. To do this, you can execute T-SQL (Transact-SQL) queries against the table or alternatively, refer to the information provided by error 2570. You can set it to any of acceptable default or specific value.

Note: You can’t run DBCC command to repair the database as it can’t determine the value to be placed instead of invalid column value.

• In case if affected rows are large in number and it is not possible to update them manually, you can restore them from backup
• If the above measure is not feasible because of backup unavailability or corruption, you need to use mdf recovery applications to safely repair and restore your database. Such MDF Repair software use powerful scanning algorithms and offer advanced repair and restoration options together with interactive user interface.

Stellar Phoenix SQL Recovery is the foremost SQL Repair tool to repair damaged SQL databases. With self-descriptive interface, advanced repair options and read-only nature, it is an effective tool to repair all database objects. It supports SQL Server 2005 and SQL Server 2000. The software is compatible with Windows Vista, XP, 2003, 2000 and NT.

After you apply the DBCC DBREINDEX T-SQL statement to rebuild one or more of the indexes for a database table in a particular SQL Server 2000 database, the indexes and statistics of the database may get damaged. This behavior results as data inaccessibility and database corruption which causes data loss. In order to resolve such behavior of Microsoft SQL Server 2000, you should opt for sql recovery solutions.

After running DBCC DBREINDEX statement on SQL Server database, the sysindexes.statblob column might get corrupted if the following conditions are true:
. The table has a computed column.
. Some columns in the table are referenced by more than one indexes or statistics.
. The table is referenced by an indexed view.
. You have re-indexed the table using DBCC DBREINDEX command.
. Auto create statistics option is enabled for database.

When you run the DBCC CHECKDB command on the database, you come across the following corruption error messages on sysindexes.statblob column:
. Server: Msg 8964, Level 16, State 1, Line 2 Table error: Object ID 2. The text, ntext, or image node at page (1:64), slot 29, text ID 6815744 is not referenced.
. Server: Msg 2576, Level 16, State 1, Line 2 IAM page (0:0) is pointed to by the previous pointer of IAM page (1:88) object ID 1977058079 index ID 2 but was not detected in the scan.

Corruption to the sysindexes.statblob column might also damage the whole MDF file.
. Sysindexes.statblob is a column of sysindexes table which is used by SQL Server to store statistics of a column or an index key. It is an image data type which stores a histogram with a sampling of values in column or index key.

The ultimate result would be the inaccessibility and loss of your mission critical data. Thus, you need to perform SQL Repair to resolve this problem and to get your data back.
This is best possible with the help of powerful third-party sql database recovery applications. These software use effective scanning mechanisms to thoroughly scan entire MDF file and extract all data from it. The software come with interactive interface and to-the-point documentation that enables you to perform SQL Repair on your own.

Stellar Phoenix SQL Recovery is the most comprehensive SQL Repair software that can repair and restore damaged MDF files in most of the data loss situations. It can repair and restore MDF files of SQL Server 2005 and 2000. The software is compatible with Windows Vista, XP, 2003, 2000 and NT.

You may face database corruption or receive an assertion in SQL Server 2000 when you use the Bulk Insert T-SQL (Transaction Structured Query Language) command or BCP (Bulk Copy Program) utility to import data. In such situations, data from SQL Server MDF files can become inaccessible. SQL Database Recovery is required to overcome such issues.

. BCP is a command line utility in SQL Server that is used in a script or batch file to automate the import and export processes through text files. To import data in a table, you must either understand the structure of table and types of data or use a format file designed for that table.

When you use BCP command line utility or Bulk Insert T-SQL command for importing data, MDF corruption may occur and you may get the following error message:

“Server: Msg 823, Level 24, State 2, Procedure gfspTMAppendText, Line 20 I/O error (bad page ID) detected during read at offset 0×0000000024e000 in file ‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL\data\<file name.mdf>’.”

You receive following information in error log of SQL Server 2000:

1. “DateTime spid53 SQL Server Assertion: File: <SpcAlloc.cpp>, line=466 Failed Assertion = ‘0′.”

2. “DateTime spid53 Error: 3624, Severity: 20, State: 1.”

If you run DBCC (Database Consistency Checker) command like DBCC CHECKDB command to find and repair corruption in the database, you receive further error messages stating:

1. “Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID 0, page ID (1:105). The PageId in the page header = (0:0).”

2. “Server: Msg 8928, Level 16, State 1, Line 1 Object ID 2009058193, index ID 255: Page (1:105) could not be processed. See other errors for details.”

Such SQL Server behavior causes critical data loss. You should figure out the cause to Repair SQL database and gain access of your lost data.

Root of this issue
You may experience assertion and MDF corruption problems, if following conditions are true:

. You use Bulk Insert or BCP utility together with the FIRE_TRIGGERS option.
. You import a database table which has an after trigger that updates the text column of another database table.

Resolution
You can fix database corruption with the help of sql database recovery software. These are highly automated tools that completely scan the corrupted MDF file and extract all of its objects as tables, stored procedures, indexes, views, triggers, constraints etc.

Stellar Phoenix sql recovery is the most advanced SQL Database Recovery tool. It can Repair SQL database components like tables, views, rules, stored procedures, defaults, user defined functions and data types along with triggers. It is compatible with Microsoft SQL Server 2005 and 2000. It supports Microsoft Windows Vista, XP, 2003 and 2000.

The logical inconsistency in the database can lead to SQL Server database corruption. It badly impacts your valuable data by making it inaccessible and results as critical data loss situations. All database applications related to back- end and front-end roles of the SQL Server database get halted. In such cases, the only way to get your data back is to opt for sql database recovery.

When you try to open your SQL Server database (MDF file), you might encounter the following error message:

“Error: 824, Severity: 24, State: 2.

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1863; actual 0:96). It occurred during a read of page (1:1863) in database ID 4 at offset 0×00000000e8e000 in file ‘F: \MSSQL.1\MSSQL\DATA\MSDBData.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.”

After this error message, when you run DBCC CHECKDB command to check and fix database inconsistency problems, you might come across further error message stating:

“Msg 945, Level 14, State 2, Line 1
Database ‘magirisilica_s24′ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details.”

This behavior of SQL Server causes data inaccessibility and data loss. To overcome this problem, you need to identify the cause of this problem and fix it by Repair SQL method.

Root of this issue

You might encounter data inaccessibility and database inconsistency problem due to MDF file corruption. Such corruption could be the result of issues like hardware problems, file system errors, virus infection, application malfunction and improper system shutdown.

Resolution

To sort out this problem and Repair SQL database to gain access of your lost data, you need to opt for sql recovery. It is the process of thoroughly scanning the damaged MDF file and extracting optimum amount of data from it.

It can be performed with the help of SQL Database Recovery software. These are highly automated tools that deploy efficient scanning methods to retrieve your data from damaged database file.
Stellar Phoenix SQL Recovery is the most comprehensive tool to ensure successful recovery of all MDF objects as tables, reports, queries, stored procedures, triggers, views, default values, rules, checks, constraints and indexes. It is compatible with Microsoft SQL Server 2005 and 2000. It supports Windows Vista, XP, 2003 and 2000.

DBCC (Database Console Commands) commands are vital for T-SQL (Transact-SQL) as they check logical and physical integrity of all objects under a selected database. The DBCC CHECKDB statement enables the user to catch and repair all the possible errors that can occur while accessing a SQL database. A DBCC CHECKDB command uses three parameters (repair_allow_data_loss, repair_fast and repair_rebuild) to perform absolute repair of the SQL database. However, there are certain issues that the DBCC CHECKDB command can not resolve. These issues make the data saved in the SQL database inaccessible. To access the SQL database and perform complete repair and restoration, if the database user has not created any back up, an efficient SQL Repair tool is required.

While shrinking the SQL database using DBCC SHRINKDATABASE command, the database user may encounter the following error message:

“Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:192) in database ID 10 is marked allocated in the GAM, but no SGAM or IAM has allocated it.”

After the above error message appears, the extent in the SQL database becomes inaccessible. An extent in SQL database is the basic unit in which space is allocated to tables and indexes. These types of error messages are normally accompanied with one or more occurrences of error 8909. Furthermore, these errors are mainly caused in multi processor environments. The main cause of the above error message is that database user is using the shrink database and reindexing operation simultaneously. To prevent the SQL database from the above issue, the database user needs to reschedule the operations to execute at different times and turn off the Autoshrink option for that particular database.

To resolve the above issue and access the SQL database, the user needs to execute DBCC CHECKDB command with repair_allow_data_loss option. This command performs complete repair and also includes allocation and de-allocation of pages for rectifying allocation errors and deletion of corrupted text. However, this repair utility can also result in loss of data. In such scenarios, the user needs to recover the lost data by using effective sql database recovery application. Such SQL Recovery software ensure complete repair and recovery of lost SQL database components by employing advanced and powerful scanning algorithms.

Stellar Phoenix SQL Recovery is an excellent sql recovery application that performs complete and orderly recovery of SQL objects. It provides interactive and intuitive graphical user interface. It is compatible with MS SQL Server 2005 and MS SQL Server 2000. It also recovers and restores the back up files of MS SQL Server.

At times when the user attempts to open the SQL Server database, he comes across an error message. This error message does not allow the user to open the database. Various factors responsible for the occurrence of this error message are less hard drive space, no permissions granted to the user, virus infection etc. In such situations, if the user has not created any back up of the inaccessible SQL database, then he needs to use effective SQL Database Recovery software.

As a practical example, when a user attempts to open a SQL Server database, he encounters the below error message:

“SQL SERVER – FIX : Error 945 Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details”

This is one of the most common error messages that the user receives while opening a SQL database. After receiving the above error message, SQL database can not be mounted. The various factors that can result in the occurrence of the above error message are less hard drive space, Autogrow feature, .mdf or .ldf files are marked as read only or the user is not granted permission to open that SQL database. To solve the above problem and to access the SQL database, the user needs to follow the following steps:

1) The user needs to add more hard drive space either by removal of unnecessary files from hard drive or by adding another hard drive with larger size.
2) The user needs to disable the Autogrow feature of the SQL database.
3) The user needs to check if the account from which he is trying to access the database has enough permission to open the database.
4) The user needs to make sure that .mdf and .ldf files are not marked as read only on operating system’s file system level.

If the above error message appears even after following the above steps, then the user needs to recover and restore the inaccessible SQL database. To do so, the user needs to use powerful SQL Database Recovery application. Such sql recovery utilities incorporate influential scanning methods to recover and restore the inaccessible and lost SQL database.

Stellar Phoenix SQL Repair is a read only sql database recovery application that ensures systematic and absolute SQL Recovery. It is designed with highly graphical user interface and supports MS SQL Server 2005 and MS SQL Server 2000. This SQL Repair utility can Repair SQL objects like, tables, views, stored procedures, rules, defaults, user defined data types and triggers. This SQL Repair application recovers and restores the backup files of MS SQL Server.

To successfully configure, manage, develop and access all the SQL server components, an integrated environment named SQL Server Management Studio (SSMS) is provided to the database users. Corruption in SSMS results in inaccessibility of SQL database components and various error messages. In such circumstances, the user needs to use effective SQL Recovery software.

For example, when a user attempts to connect to the SQL database and opens SSMS, he encounters this below error message:

“Database ‘msdb’ cannot be opened. It has been marked suspect”

The main cause of the above error message is that the SQL database has been suspected. This situation occurs under following conditions:

• When the error log file does not match with the database file
• SQL database corruption

When the user views the SQL Server application event log, he finds the following entry:

“The log scan number (152:284:1) passed to log scan in database ‘msdb’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.”

Once the above error message appears on the screen, SQL database becomes inaccessible. To resolve the above problem, the user can use an inbuilt tool named sp_resetstatus. This tool enables the user to change the SQL database status and access the SQL database again. But in this case, this inbuilt tool will not be able to resolve the issue. This tool will only allow the user to connect to the SQL Server instances and will let you access other databases. To resolve the above error message and perform complete sql database recovery, the user needs to use a third party application known as SQL Recovery software.

Stellar Information Systems, the globally leading data recovery company provides the finest SQL Recovery software – Stellar Phoenix SQL Recovery. This SQL Repair application provides a comprehensive solution to repair SQL database components such as, tables, views, stored procedures, triggers, queries etc. by incorporating influential algorithms. This SQL Repair application is a high end repair utility that can recover and restore the backup files of MS SQL Server. This sql recovery application supports MS SQL Server 2005 and MS SQL Server 2000. The demo version of this SQL Repair utility is available on Stellar’s website.