Feed: MDF File Recovery - AggScore: 45.2

SQL Server 2008 is the best tool for database developers and administrators as it incorporates some of the major improvements over the earlier versions. It has support for large data sets and the ability to handle multiple users. The new 'Performance Studio' provides you with a collection of tools for easy monitoring and troubleshooting the system. It allows you to build up an efficient policy management system and help database administrators in carrying out various administrative tasks. Regardless of all these benefits, there are various contingencies that lead to corruption in the SQL databases. The potential reasons behind database corruption include virus infection, hardware failures, operating system bugs, etc. As a consequence, all the valuable objects in the SQL database become inaccessible. In order to recover your lost invaluable data, you should perform SQL recovery through proficient third-party software.


IAM chain corruption is a common occurrence on SQL Server 2008. This is usually accompanied by the below given error by DBCC CHECKDB:


"Msg 2576, Level 16, State 1, Line 1 
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:153) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594042384384 (type Unknown), but it was not detected in the scan."


The system table 'sysallocunits' usually has a reference to the first IAM page, the root page, and the first page. The above error says that metadata does not include a reference to the first IAM page in the IAM chain (page 1:153). This indicates corruption in the 'sysallocunits' system table.


If you try to use the 'repair_fast' and 'repair_rebuild' options in this case, you may get no significant results. In order to resolve the issue, you may try the following series of steps:

•     Start a transaction
•     Write the rowcounts for all system objects and tables in your database.
•     Now use CHECKDB for repairing the database.
•     Make sure that the problem is resolved and the error is not seen again
•     Rerun DBCC CHECKDB to verify the database.
•     Examine the rowcounts once again.
•     Commit the transaction

The 'repair_allow_data_loss' should be the minimum repair level for this case. If the problem persists, you may try professional SQL 2008 database recovery software. These third-party tools extensively scan SQL databases to recover damaged or inaccessible objects, including tables, views, indexes, constraints, triggers, etc. Moreover, they are compatible with Windows 7, Server 2008, Vista, Server 2003 and XP.

• Don't Panic
• Keep Patience
• Take Backup of your database
• Check hardware configuration

• Check Hardware Configuration
• Restore from Backup
• Run DBCC CHECK with repair_allow_data_loss

• Make sure that your system has all the necessary device driver files.
• If your SQL database is corrupt, you need to follow the given procedure.

• First, drop and recreate all indexes in your database.
• Create a new database table and copy all records from the original table to this newly created table.
• Now, update the statistics.
• Run the following command:

• Finally, run the 'sp_recompile' procedure

Space Full

 

• Backup the transaction log.
• Issue some disk space.
• Move the log file to a disk drive (secondary storage media) with sufficient space.
• Enlarge the log file.

Database Users or administrators can start Microsoft SQL server database from the task bar. To start, go to the task bar, open sq server service manager and then click on the start button. Sometimes MS SQL server is not start through this method and give some error message like

2011-11-20 10:15:39.87 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)

Most possible reason for the above error message is “model is corrupt”. To resolve this, you can create a new copy of tempdb. This will fix the starting problem of SQL server database. If you still face the same problem then first of all make the backup of all MDF & LDF files.

How to Make Backup when Unable to Start SQL Server? As we have discussed, you are unable to start sql server then take a backup of data & log files by below steps:

• Set SQL server service manual by Windows control panel.
• Reboots the windows.
• Copy all the data and log file to the another computer or any removable media.

SQL Server transaction log file is used to store recent database transactions. When you modify or alter any entity in SQL server database then it will be result in a write action in the transaction log file. Generally we do several activity on the SQL server database like add, delete, rename, update, insert and many more and this will also be the result of write action in the transaction log file but a read action on the SQL server database does not change the transaction log file.

Does recovery models affect transaction log? 

• The answer is NO. Recovery models (simple, full & bulk-logged) do not affect what has written into the transaction log but it effects the time period of entries that present in the transaction log, detail description is as follows:

1. Simple Recovery Model: In this recovery model, transaction log entries are keeping for the database integrity not for the database recovery purpose. When we run checkpoint operation on the transaction log files then all the inactive entries are deleted and space is ready for reuse. It is also the best way to manage the size of transaction log file but you can prefer another way also to manage size of transaction log file.
2. Full Recovery Model: In this recovery model, entries are key for the both integrity as well as for database recovery purpose. All the entries are remains present in the transaction log file until a log backup does not perform on the file. Sometimes it difficult to manage the size of transaction log files through this recovery model.  
3. Bulk-logged Recovery Model: It is almost same as full recovery model but in this recovery model bulk operations are minimally logged.

• You can monitor the size of transaction log file by DBCC SQLPERF command. When will you run this command then it returns information about amount of log space used currently and it also indicates when transaction log is in need to truncate. Log truncation frees the used disk space but does not reduce the size of physical transaction log file. To reduce transaction log file size, you will have to shrink the log file.

• You can shrink the transaction log file manually by the using of DBCC SHRINKFILE command. This command may be differ for the different SQL server database.

• We have created a database with same name and same SQL data files (MDF & NDF).
• Make the file size identical as original SQL server database
• Stop the SQL server database services.
• Rename the old MDF to new one and copy the old MDF to new MDF location
• Delete the LDF files.
• Start SQL server database services
• Our database has gone in suspect mode.
• Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up Sp_configure “allow updates”, 1
  go
  Reconfigure with override
  go
  Update sysdatabases set status = 32768 where name = “corruptDBName”
  go
  Sp_configure “allow updates”, 0
  go
  Reconfigure with override
  go
• Now restart SQL server database.
• Our database is in emergency mode.
• Run DBCC CHECKDB on our database with repair_allow_data_loss, that will rebuild the log files and full repair.
• Again restart SQL server database and now our database is online.    

1. Set the database off-line through this command

2. Move the files to a designation location

3. Now set the database online through this command

4. Verify the changes

MS SQL server database offers a high performance backup and restore facility to its database users for maintaining the backup of SQL server database and restore in case of corruption. SQL server database may be corrupt due to severely reasons like media failure, user errors, power failure, metadata structure corruption, virus attack and many more. High performance backup and restore facility enables database administrator to handle all above corruption issues in SQL server database. A well planed backup and restore strategy helps database administrator to protect their data in case of above mentioned failures. The point comes to describe backup.

Backup: A copied data that can be used in restoring the corrupted data is called backup.

How to make a backup of SQL server database? : A database administration can take a backup of SQL server database with the help of SQL server management studio. Below image is the screen-shot to make a backup in SQL server database.




Restore SQL server Database in case of Corruption?: A database administrator can restore corrupt SQL server database with the help of good backup but he/she will be also able to restore corrupt SQL database without any backup. In this article we will discuss about both two methods for restoring corrupt SQL server database.

Restore with Good Backup: A database administrator can use “backup & restore” method, if he has maintained a good backup for his database. Two machines are required in this method. One is source machine where corrupt database reside and another is designation machine where corrupted database will be restored. 

Note: Make sure following point before restoring the database

• The designation machine has sufficient space to restore.
• Directory structure on designation machine is must to exist.
• Don't have same file name on the designation machine.

When you are trying to start the services of SQL server database and found the database is unavailable. At this situation you are unable to do any operation on the SQL server database like insert, delete, rename, update, and many more or even unable to open the SQL server database. You can open and see SQL server error log to know the reasons for the problem. After knowing the reason, you can take appropriate method to recover sql server database. When you open the error log got an error message. The error message is:

"FCB::Open failed: Could not open file for file number . OS error: 5(access denied)"

Possible Cause: There are tons of reasons for the above problem but we will discuss here about hardware failure problem and account access problem.

Hardware Failure Problem: Whenever you will get the above error message. It is recommended you to check the hardware component of your system first before try any other action. If you found any faulty hardware component then replace it with new one and fix the problem.

Account Access Problem: The account in which SQL server is running does not has permission to access the folder that contains data and log files.   

Follow Steps to Fix: You can fix the above error message by following the below steps:

• Click on the Start button of your system
• Go to the programs and select SQL server
• Click on the folder that contains one the database files
• Right click on the folder
• Select sharing and security
• Now, select security button
• Click on the Add button
• A text box will be appear
• Write qualified user-name of the service account in the text box
• Check the allow column to ensure full control
• Repeat these all steps for all other database and log folder.
• Shutdown the SQL server database
• Now, restart the SQL server database from configuration manager.

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.

“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.