How to repair MS SQL server 2005 Database?

In this article, I have described about how to repair a corrupt “MS SQL server 2005 (compact edition) database” with the help of engine object repair methods.

MS SQL server 2005 compact edition database has several files and all these files are divided into four kilobytes unit each, these files are known as pages. SQL server compact edition database stores a checksum for all pages. If a page is corrupted or damaged then checksum of the page does not match with stored checksum. There are so many reasons for the corruption in the database file like Meta data structure corruption, virus attack, sudden system download, hardware failure and many more. If you have verified that there is a corruption in the database file then you can repair sql server database file by the engine object repair method.

You can verify corruption is the database file by calling system.data.sqlserverce.sqlceengine command. If this command returns a true value then there is no corruption in the database file and if this command returns a false value then there may be some corruption in the database file. The syntax for verifying the corruption is given below:

Syntax for verify

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

If a database file has been corrupted then you can recover database file by engine object repair methods. This method scans and fixes the corrupt database file. Engine object repair method provides two repair methods:

1)  repairoption.deletecorruptedrows
2) repairoption.recovercorruptedrows

Repairoption.Deletecorruptedrows: This method discards all the corrupted pages and recovers the database files but you may lose some data if corrupt database file contains the database schema.

Syntax for this method
engine.Repair(null, RepairOption.DeleteCorruptedRows);

Pros: Guaranteed that recovered database is free from logical corruption.
Cons: You may lose some data, if you will use this method.

Repairoption.recovercorruptedrows: This method tries to read more data from corrupted database files and recover more data as can possible.

Syntax for this method
engine.Repair(null, RepairOption.RecoverCorruptedRows);

Pros: Recover more data in comparison with repairoption.deletecorruptedrows.
Cons: Does not guarantee that recovered database is free from logical corruption.

Read More

Analyzing DBCC CHECKDB failure in severe corruption cases and MDF Database Recovery

The DBCC CHECKDB utility is used more often than not by almost all SQL database users. This eminent utility makes use of an internal database snapshot to obtain transactional consistency of the database. It uses this information to check and repair SQL database following a three stage process. In the first stage, it performs an allocation check. Then it checks all the critical system tables for consistency errors in the second stage. In the final stage, a consistency check of the whole database is performed. Sometimes due to severe corruption, the process cannot be completed. If the CHECKDB utility fails to repair corruption, you should go for SQL recovery through a reliable third-party software.

For instance, you use MS SQL Server 2000. While running CHECKDB on one of your important database files, you notice that the process terminates unexpectedly giving you the following error message:

“Database 'DBNAME' consistency errors in sysobjects, sysindexes, syscolumns, or systypes prevent further CHECKNAME processing.”

Cause:
The CHECKDB utility encountered some errors in the second stage that it cannot repair. So, it simply aborts the process before reaching the third stage and throws the above specified error message. The completion of the second stage is necessary for executing the third stage.

This could have happened due to metadata corruption in the database system tables. Corruption may make all the components in your database inaccessible. You may use a backup to restore all the lost or inaccessible data. If the backup is missing, you should follow the below mentioned resolution steps to perform MDF file recovery.

Resolution:

Try the following methods to resolve the problem-

Check the SQL Server error log and the Windows application and system log to determine the cause of the problem. If hardware failure caused the problem, run the hardware diagnostics tool.
Run DBCC CHECKDB with proper repair clause to fix corruption.

If the problem still persists, you should take help of a SQL database recovery software. These software are capable of recovering the damaged SQL database components in their original form restoring all their properties and relationships with other components. They retrieve tables, views, queries, indexes, stored procedures, keys, constraints etc. from the corrupt database.

Read More

How to Recover Database When MDF file get corrupted ????

The data loss from the database is a very panic situation that occurs without any warning message. It can corrupt or damage our data stored on our hard drives or on any storage media. The database may be corrupted due to hardware failure, application failure, improper system shutdown, media read errors, software failure, power failure, virus attack and many other reasons. In such case you need to use an advanced database recovery software to recover and repair corrupted database. In case of MDF file corruption, you may face any of the given below error messages:-

“Windows could not start the SQL Server”
“MDF is not a primary database file”
“Server can’t find the requested database table”
“Database Can’t Be Accessed”
“The file *.mdf is missing and needs to restore”
“The execution of a full-text query failed”.

Solution: To repair and restore corrupted .mdf file by any of the above error message follow given below steps:

Fix Hardware Issue: To fix the error message check your hardware setup if found any problem with the hardware then repair or replace it by new one and fix the problem.

Run DBCC CheckDB Command: If the problem is not related to hardware then run dbcc checkdb command without any clause and then run again dbcc checkdb command with the proper repair clause.

Restore from Backup: After running the dbcc checkdb command and you still getting an error message then restore database from the latest and updated backup. If you have no any backup then you should try an advanced mdf recovery utility to repair and restore corrupted .mdf file.

Read More

Reliable mdf repair utility for risk-free recovery of your SQL server database

If you are using a Microsoft SQL database for storing your valuable business transactions and financial information, then, any instance of database corruption can be a huge blow to your business. The damage to the database can result in the complete inaccessibility of the data stored n it. In such cases, you can run ‘DBCC CHECKDB’ from the command-line, which can repair and fix the MS SQL database errors. If with higher degree of corruption, the database DBCC CHECKDB fails to resolve the problem, you can always run an reliable SQL repair utility to accomplish the job safely.

Following are some of the error messages, which you may encounter, while trying to fix them with DBCC CHECKDB command:

“Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:6945928) in database ID 8 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:6945936) in database ID 8 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:6945944) in database ID 8 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

The command fails to resolve the issues and further flashes the below messages:

Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.”

Cause:
The MS SQL Server database has been severely damaged and DBCC CHECKDB is unable to repair the same.

The best possible way to get the database and the data back is to restore it from a valid backup. If you have not taken any backup recently or there are any unresolved problem in restoring it from the backup server, then, you have to look for any good mdf repair utility.

These SQL recovery software are meant to repair the corrupt SQL database and recover the tables, schema, triggers, containers etc with their originality being intact.

Stellar Phoenix SQL Recovery is a trusted utility to safely repair SQL database and recover your data without any alteration to the database. This efficient utility is capable of repairing the MS SQL Server 2008, 20005, 2000 databases and is completely compatible with Windows 7, Vista, 2008, XP, 2003 and 2000 operating systems.

Read More

Use Advanced SQL Recovery Tool to Repair MDF Files

If you are regularly using Microsoft SQL 2008, then you might have faced several situations wherein you are unable to mount a SQL Server database. Such cases are sometimes cause very serious problem such as database corruption or complete inaccessibility of the SQL database thereby making your database very insecure. These corruption scenarios can arise due to various reasons such as automatic system shutdown when the database is running, power failure, virus infections, hardware issues, human errors etc. In such cases, you should find the cause of database corruption and try to repair SQL. If you are unable to repair SQL(.MDF) file, then you should use an advanced MDF file repair software to perform SQL database recovery.

Consider a Practical scenario wherein you try to open a SQL database while using MS SQL Server 2008. However, database does not open. An error message appear as a result, which is:

"Database '%.*ls' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details."

Cause:

The root cause of above error message is that the database could not be mounted because some resources or files are missing.

Resolution:

To resolve above error message, you should repair MDF file. To do this, you need to perform the given below methods:

Check Error log: Firstly, You should check error log to get the details about resources such as disk space, memory, permission failure, etc that may have caused the above problem.

Confirm the location of SQL Database files: You should check the location of MDF or LDF files and verify whether Database Engine account has required credentials to access these SQL Database files.

Restart Database: Finally, you should restart the SQL Server database with the help of ALTER DATABASE query. This query sets the SQL Server database to online.

Above described methods should be able to set the SQL database up. However, if you still are unable to mount the database then you should use a SQL repair tool to repair SQL Server database. Such MDF database repair software are read-only in nature.

Stellar Phoenix SQL Database Recovery software is an SQL database repair tool that repairs MDF file and NDF file alike. Specifically designed for MSSQL Server 2000, 2005, 2008 and 7.0. This MDF file repair tool restores various database objects such as collations, tables, stored procedures, triggers, defaults and many more. In addition, this repair mdf software preserves the relationship among the database tables.

Read More