Attach in emergency mode - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Attach in emergency mode

Author

Topic

syno
Starting Member

6 Posts

Posted - 2008-11-25 : 13:44:22

Hello

I have received some LDF and MDF files from a customer which I have attached to MSDE doing an emergency mode repair. I am using SQL Server Management Studio Express. However when I run DBCC CHECKDB against the corrupted database I get the following error messages on the tables:

'Unable to process table SA76 because filegroup ReadOnly is invalid'

Is there anyway to recover this database. Any help on this would be much appreciated.

Thanks

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-11-25 : 13:56:23

Can you post the entire output of

DBCC CHECKDB (yourdb) WITH ALL_ERRORMSGS, NO_INFOMSGS

Looks like you may have an incomplete set of files from your customer and its missing a filegroup.

I'm assuming the customer doesn't have any backups?

Why are you using emergency mode repair? Is the transaction log file damaged?

Thanks

Paul S. Randal, SQL Server MVP
Author of SQL 2005 DBCC CHECKDB/repair code
Author & Instructor of Microsoft Certified Master - Database course
Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)

syno
Starting Member

6 Posts

Posted - 2008-11-25 : 16:23:42

Hi

I should have mentioned that I also have a DBNAME_ReadOnly.ndf file which I have not attached because when I do I get the following error message when I refresh the database:

TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

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

------------------------------

A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 1 - I/O Error detected in read/write operation) (Microsoft SQL Server, Error: -1073741769)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1073741769&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
However if I run:

DBCC CHECKDB (DBname) WITH ALL_ERRORMSGS, NO_INFOMSGS

Without the DBNAME_ReadOnly.ndf file I get the following for all databases:

'Unable to process table SA76 because filegroup ReadOnly is invalid'

Sorry I am unable to re attach the databases again after add the DBNAME_ReadOnly.ndf file as I get the following error:

TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Failed to connect to server SIMONSYNO\SAGETAX. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

I don't know if the log file is corrupt and no the customer does not have any other backups.....Do you think it would be more worth while trying to attach with the DBNAME_ReadOnly.ndf file as well.

Thanks

syno
Starting Member

6 Posts

Posted - 2008-11-25 : 16:29:03

Hello

I have just managed to re attach them. The error message I get without the DBNAME_ReadOnly.ndf is:

Msg 2519, Level 16, State 1, Line 1
Unable to process table SA76 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table BT22 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA12 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA13 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table TT21 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA22 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table CountryCodeNote because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table CountryCode because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA33 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table DefaultIB20 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA38 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA11 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA14 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA21 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA24 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA29 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA35 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA37 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA51 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA52 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA53 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA54 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA55 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA56 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA57 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA58 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA59 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA60 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA61 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA62 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA63 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA64 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA75 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA65 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA66 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA80 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA67 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA68 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA69 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA70 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table BT23 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table BT26 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table BT64 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table BT27 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table BT65 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table DefaultIB33 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table CT21 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table DefaultAD02 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table CT23 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table DefaultAD03 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table CT26 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table DefaultAD05 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table DefaultAD06 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table TT23 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table DefaultAD07 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table DefaultAD08 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table DefaultAD19 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table DefaultAD20 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table DefaultSA36 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table IB36 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table DefaultIB32 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table IB73 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table IB30 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table BT21 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table IBDE because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table BT62 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table IBDT because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table DefaultReturns because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table IBDD because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table IBFK because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table Schedules because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table IBRFO because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table Scripts because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table RatesAndParameters because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table IBRTO because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table AD21 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table PageData because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table IBRPO because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table AD22 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA151 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA152 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA153 because filegroup ReadOnly is invalid.
Msg 2519, Level 16, State 1, Line 1
Unable to process table SA154 because filegroup ReadOnly is invalid.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-11-25 : 17:17:57

Right - all the tables on the read-only filegroup are inaccessible, because you've attached the database without that filegroup.

Try to follow my attach-a-suspect-database trick to get the entire database attached - see [url]http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx[/url]

Paul S. Randal, SQL Server MVP
Author of SQL 2005 DBCC CHECKDB/repair code
Author & Instructor of Microsoft Certified Master - Database course
Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)

syno
Starting Member

6 Posts

Posted - 2008-11-26 : 17:56:26

Hello again

OK I have now attached my corrupt databases and I can view the contents in emergency mode. When I take the database out of emergency and refresh i can still see the contents of the database, however when I run script against it I get the following error message:

'Msg 3908, Level 16, State 1, Procedure sp_change_users_login, Line 196
Could not run BEGIN TRANSACTION in database 'DBName' because the database is in bypass recovery mode.
The statement has been terminated.'

When I stop and start the service to try and take it out of by pass recovery mode I cannot connect and get the error message:

'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)'

Thanks

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-11-28 : 13:33:26

Once you get the server running again (having a corrupt user database should not prevent the server from starting), put the database back into emergency mode and run the following

DBCC CHECKDB (yourdb) WITH ALL_ERRORMSGS, NO_INFOMSGS

And post the output.

Btw - if you're trying to do data recovery for a client, with all due respect, I think you should pass along the files to someone with more experience recovering data from databases damaged in this way - or possibly call Product Support to help you in real-time rather than waiting days for forum responses.

Thanks

Paul S. Randal, SQL Server MVP
Author of SQL 2005 DBCC CHECKDB/repair code
Author & Instructor of Microsoft Certified Master - Database course
Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources