<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:70.85pt 70.85pt 70.85pt 70.85pt; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->

Hi,

I have an issue with restoring a SQL 2005 DB on MSSQL 2008 Server. It throws the following error message:

"Restore failed for Server 'THE_NAME_OF_THE_SERVER'. (Microsoft.SqlServer.SmoExtended)"

Additional information:

An exception orruced while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Possible schema corruption. Run DBCC CHECKCATALOG. (Microsoft SQL Server, Error: 211)

The restoring of the same DB on MSSQL 2005 is working, but the command DBCC CHECKDB ('DB_NAME') WITH NO_INFOMSGS, ALL_ERRORMSGS returns the next error message:

Msg 211, Level 23, State 157, 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.

After executing the DBCC CHECKCATALOG command I received the following message:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Please advice, how can I solve the corruption issue.

Thanks

Gyuki

Most probably you are in for hunting down a healthy copy (backup) of that database. I assume you run regular DBCC CHECKDB, and through that can determine when you had the most recent execution without that error message. Use the most recent backup *before* that. You can of course open a case with MS Support and see, but error 211 is not looking good. See for instance http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-demo-corruptions-fatal-to-DBCC-CHECKDB.aspx(search the page for 211).

+1

Once you have corruption, even if DBCC CHECKDB can fix the corruption, you have almost 100% certainty there's data loss. As TiborK says, you'll need to hunt down a copy of the backup that doesn't have corruption or accept that some data is just gone.

Its true that in most of the cases when the DBCC CHECKDB fails to fix the problem present in the Microsoft SQL database then there are very less chances that the data will rescue untill or unless you use some third party sql database recovery applications. There are several Data Recovery Companies that are making applications which can recover data on different platforms and from different versions of SQL. So according to the SQL application you can choose the right application.

Also there are applications that support various versions of Microsoft SQL Server application and so those are good if you exactly don't know the version of SQL you are using. I found the application from here, http://www.sqldatabase-recovery.com

It was nice and recoved all my corrupted table, triggers and all other things that were corrupted due to immense virus attack last month.

This post is now old, but it wouldn't be complete without me adding that, if your corruption lies within your non-clustered indexes then it would be possible to NOT have data loss. So the very first step when you detect corruption is to identify where it lies.

You need to fix the corruption first. Secure your backup copy first.

Restore the database on to a server and make an attempt to repair. once the corruption is fixed then compare the number of rows in each table. If records match you may not have any loss how ever data in the columns might lost.

From the above error it looks like it is not a index page corruption. you are less likely to loose data.

here is the code

ALTER DATABASE [yourdatabase name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DBCC CHECKDB ('yourdatabase name', REPAIR_ALLOW_DATA_LOSS);

ALTER DATABASE AdventureWorks2008R2 SET MULTI_USER;