Home >> How to Recover SQL Server 2008 R2 Database

 

How to Recover SQL Server 2008 R2 Database

 

There are a number of factors that can bring about corruption in SQL Server databases. Any level of corruption can wreak havoc and incur much downtime. Generally, an SQL database becomes corrupt due to problems in an external hardware source like hard disk controller. MS SQL Server 2008 R2 uses a feature called 'Page Checksum' to identify problems caused due to these failures. It calculates a checksum value for every database page, which is either read from or written to the disk. If these page checksums do not match, then the database goes into the suspect mode.

 

When you migrate your production database from an ‘SQL Server 2008’ database server to a new SQL Server 2008 R2 server, you may run across several DBCC allocation errors while performing backup jobs. As a result, your database maintenance plan fails and you get a long list of errors similar to the one shown below:

 

 

'Msg 2534, Level 16, State 2, Line 1 Table error: page (1:1100030), whose header indicates that it is allocated to object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data), is allocated by another object.'

 

To fix this error, you should run 'DBCC CHECKALLOC' with a repair option. Generally, the repair option used for allocation errors on the database is 'REPAIR_ALLOW_DATA_LOSS'. This facilitates allocation and de-allocation of rows and pages for fixing these errors.

 

Additionally, when you run DBCC CHECKDB on an SQL Server 2008 database that has been restored from SQL Server 2000, you may receive several consistency errors on the database tables as follows:

 

'Msg 211, Level 23, State 51, Line 1 Possible schema corruption. Run DBCC CHECKCATALOG.'

 

'Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.'

 

These errors are seen due to a possible corruption in the database tables that was not reported by the earlier version of DBCC CHECKDB. The SQL Server 2008 version of this tool runs DBCC CHECKCATALOG, which is not executed by the 2000 version. You can run DBCC UPDATEUSAGE to fix this.

 

Usually, you should follow this approach to get your SQL Server 2008 R2 database out of the 'suspect' mode and perform SQL 2008 R2 recovery:

  • Initially, turn off the suspect flag on your database: EXEC sp_resetstatus ‘yourDBname’
  • Set your database to emergency mode and run DBCC CHECKDB: ALTER DATABASE yourDBname SET EMERGENCY DBCC checkdb(’yourDBname’)
  • Switch to single-user mode and again run DBCC CHECKDB: ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB (’yourDBname’, REPAIR_ALLOW_DATA_LOSS)
  • Get back to the multi-user mode as follows: ALTER DATABASE yourDBname SET MULTI_USER

 

If the above methods are unable to fix database corruption, use Stellar Phoenix SQL Database Repair. This SQL Server 2008 R2 recovery tool uses safe, proprietary methods to repair your damaged SQL database and recover every object with its original set of properties. The software smoothly restores all your database tables, views, indexes, keys, constraints, triggers, user-defined functions, stored procedures, etc.

 

Stellar Phoenix SQL Database Repair employs a simple, three-step procedure to repair and recover SQL Server 2008 R2 databases.

  • First, the software allows you to select the corrupt database from a known location or search for a specific database in your system.
  • Next, it scans the entire database and displays all recoverable objects in a tree.
  • Finally, it saves the recovered file to a user-specified location.

 

This utility supports recovery of MDF and NDF files created in MS SQL Server 2008 R2, 2008 7.0, 2005 and 2000.

 

 

free download