Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Attach Database failed for server 'M3' Expand / Collapse
Author
Message
Posted Saturday, May 26, 2012 5:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 31, 2014 12:28 PM
Points: 50, Visits: 221
Dear Sir,

I am in great trouble, at present I have no back of the database, while i attaching database in the sql server 2000, the Sql server giving error message which was mentioned below within single quotes.

'Attach database failed for Server 'M3'. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch.

I/O error (torn page) detected during read at offset 0x0000003d0f4000 in file 'D:\TEST\DS1213.mdf'.
Error while redoing logged operation in database 'DS1213'. Error at log record ID (1081:4550:10).
Could not open new database 'DS1213'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 823)'

how to attach the database I don't know please help me anybody

Thanking You

Debasis Bag
Post #1306941
Posted Saturday, May 26, 2012 5:19 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 39,866, Visits: 36,208
No backup? Seriously why the hell not?

Your database is corrupt, it may be possible to get it back with some data loss, it may not. You need to 'hack' the database back into SQL Server.

Copy those files somewhere safe for the moment.
Create a new database with the same file structure (same number of files, same logical names) as the files for the DB you can't attach.
Stop SQL Server
Delete the files for the newly created database and put the files from that damaged database in their place.
Restart SQL Server. The database will probably come up suspect.

Once you're that far, post the error log entries and the output of querying sysdatabases for that DB. DO NOTHING ELSE AT THIS POINT.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1306942
Posted Saturday, May 26, 2012 8:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 39,866, Visits: 36,208
Please post the outcomes and messages here, not elsewhere. Keeping things in one place makes problems far easier to solve.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1306968
Posted Saturday, May 26, 2012 8:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 10:41 PM
Points: 38, Visits: 440
Try attaching to Higher Version of SQL server, if attaching works fix it. detach it and attach it back to SQL Server 2000.

I'm not sure, that this will work. But worth giving a try.
Post #1306970
Posted Saturday, May 26, 2012 8:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:16 PM
Points: 31,018, Visits: 15,456
Try attaching to Higher Version of SQL server, if attaching works fix it. detach it and attach it back to SQL Server 2000.


That will not work. Once you attach to a higher version, you cannot ever attach it back to a lower one.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1306972
Posted Saturday, May 26, 2012 8:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 10:41 PM
Points: 38, Visits: 440
I haven't done that before..

I'm aware that backups can't be restored to lower version and not about this.

Thanks for your inputs...
Post #1306973
Posted Saturday, May 26, 2012 8:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 39,866, Visits: 36,208
seven.srikanth (5/26/2012)
Try attaching to Higher Version of SQL server, if attaching works fix it. detach it and attach it back to SQL Server 2000.

I'm not sure, that this will work. But worth giving a try.


Not only won't it work, but it's far less likely that a damaged database will attach to a higher version than to the version it was detached from and if it does by some small miracle work, the OP would not be able to get the DB back to the lower version.

Please don't give advice in the data corruption forums that can make a situation worse.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1306974
Posted Wednesday, May 30, 2012 2:03 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:23 PM
Points: 114, Visits: 1,222
The I/O error (torn page) error occurs when SQL Server tried to access a page that had previously not been written to disk correctly. This can be happen due to power failure, disk failure, hardware failure when the disk is being written to.

When this error occurs, sql server ended your connection. If the torn page was detected during database recovery, then SQL Server will mark it as SUSPECT.

Solution: First Check your error logs and then restore your database form last good backups and transaction logs. It will definitely fix the problem, but you should also check your disk and fix any issues if there was a disk hardware failure. You can also use third party sql database recovery software to resolve this error.


SQL Database Recovery Expert
Post #1308146
Posted Wednesday, May 30, 2012 3:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 10:03 AM
Points: 1,096, Visits: 1,334
prettsons (5/30/2012)
The I/O error (torn page) error occurs when SQL Server tried to access a page that had previously not been written to disk correctly. This can be happen due to power failure, disk failure, hardware failure when the disk is being written to.

When this error occurs, sql server ended your connection. If the torn page was detected during database recovery, then SQL Server will mark it as SUSPECT.

Solution: First Check your error logs and then restore your database form last good backups and transaction logs. It will definitely fix the problem, but you should also check your disk and fix any issues if there was a disk hardware failure. You can also use third party sql database recovery software to resolve this error.

The OP has said that there are no backups :-(


Adam Zacks

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

Be Nice, Or Leave
Post #1308182
Posted Wednesday, May 30, 2012 3:24 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:23 PM
Points: 114, Visits: 1,222
Schadenfreude-Mei (5/30/2012)
prettsons (5/30/2012)
The I/O error (torn page) error occurs when SQL Server tried to access a page that had previously not been written to disk correctly. This can be happen due to power failure, disk failure, hardware failure when the disk is being written to.

When this error occurs, sql server ended your connection. If the torn page was detected during database recovery, then SQL Server will mark it as SUSPECT.

Solution: First Check your error logs and then restore your database form last good backups and transaction logs. It will definitely fix the problem, but you should also check your disk and fix any issues if there was a disk hardware failure. You can also use third party sql database recovery software to resolve this error.

The OP has said that there are no backups :-(


I shall suggest to Debasis Bag to try third party sql database recovery software.


SQL Database Recovery Expert
Post #1308189
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse