MSSQL Database Recovery

BCP is the SQL Server utility that you can use to copy bulk data between a data file and instance of MS SQL Server. You can also use it to import/export data in/from SQL Server tables. But sometimes when you perform a bulk copy operation, it corrupts index pages of unique nonclustered index. It occurs when this operation results into duplicate rows insertion into a table. In such situations, you might receive an error message that the table is corrupt. Thus, to restore the missing information, you should use your last backup. It might occur that you don’t have such backup that could give required results. To cope up such problems, you can use powerful SQL Recovery software.

When you run DBCC CHECKTABLE command to check SQL table consistency, you might receive below errors:

Server: Msg 8951, Level 16, State 1, Line 0
Table Corrupt: Table ‘table_name’ (ID 1797581442). Missing or invalid key in index ‘IDX_2′ (ID 2) for the row:

Server: Msg 8955, Level 16, State 1, Line 0
Data row (1:97:0) identified by (RID = (1:97:0) ) has index values (Col1 = 1 and Col2 = 1 and Col3 = 12 and Col4 = Sep 30 2000 12:00AM!t~0WeG).

Cause

The most probable cause of such error messages to occur is that you have used bulk copy operation that has corrupted index pages of unique nonclustered index. It occurs when bulk copy operation or DTS (Data Transformation Services) import package inserts duplicate rows into a table, when any of the following condition exists:

1. The input file you are using contains duplicate rows
2. The table to be targeted doesn’t contain any clustered index and contains a unique nonclustered index that has been created with ignore_dup_key option
3. You have turned on the Select Into/Bulk Copy option

Solution

Apply any of these methods to fix such issues:

1. You should clean the target table by dropping nonclustered index. You can then remove the duplicate rows and recreate the unique nonclustered index
2. If problem persists, you can use commercial SQL Repair software to repair and restore table contents. Since using DBCC CHECKTABLE command with repair option cannot correct this issue, using SQL database recovery software is the only way lefts.

Stellar Phoenix SQL Recovery is an advanced tool to repair and restore damage SQL databases and its components. It supports safe SQL Repair for SQL Server 2008, 2005 and 2000. It can restore database tables, triggers, stored procedures, views, rules and other database components. It is compatible with Windows Vista, XP, 2003, 2000 and NT.

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.

Stellar Information System Ltd. introduces SQL Recovery software. The data recovery company, which has achieved its customer’s satisfaction up to a peak level through its topmost data recovery services and products, has now come up with new software which can recover and repair corrupted Microsoft SQL server Database file. Until now Stellar is known for its quality products and this MS SQL Repair application is in the line of its next achievement as it can restore the damaged .mdf files for instances like Virus attack, system shutdown, media read error and so on. This software would prove as a boon to all those who are facing data loss due to SQL database corruption.

Stellar Phoenix MS SQL Data Recovery software-launched by Stellar today, is the best solution for repairing corrupted MS SQL server databases. The repair software that are already available in the market for repairing databases on other platforms like MS Access are gaining much popularity for their tremendous performance and now this MS SQL Repair software is an addition to them. It has been developed seeking the comfort l zone of the users during its use and very well adapted to scan thoroughly and completely the corrupted .mdf files in order to extract as much data as possible. The user will never discover the original data deleted or modified after the use of this software and also is safe and non-destructive.

The key features of the product are listed below:

• Recovers Tables, Views and Rules that has been there in the database.
• Recovers defaults.
• All the user defined functions and data types are recoverable.
• An interactive user interface.
• The stored procedure can also be retrieved.
• Works well with Windows 2000, 2003, XP and Vista.
• Compatible with MS SQL Server 2000 version.
• Can recover indexes and constraints.

This MS SQL Repair software can repair the database after:

• Corruption due to virus attack
• Corruption due to application malfunctioning.
• Improper system shutdown.

This MS SQL Repair software goes good repairing the MS SQL server databases after any instance of database corruption and with any file version of Windows and thus acting as a complete solution for your corrupted SQL databases.

Stellar Phoenix MS SQL Data Recovery software comes in two versions-Demo and full version. The preview of the repairable SQL database files can be obtained by its demo version which can be downloaded from http://www.mssqldatabaserecovery.com/download-sql-database-recovery-software.php.Whereas for having the actual output of the file, you will have to buy the product from http://www.mssqldatabaserecovery.com/buy-now.phpat a minimal cost of US $ 399. For more detailed features and abilities of the software visit: http://www.mssqldatabaserecovery.com

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.