Attach Database failed for server 'M3'

Question

Attach Database failed for server 'M3'

deba_20032004

Default port

Points: 1476
More actions
May 26, 2012 at 5:12 am

#274700

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
May 30, 2012 at 2:03 am

This was removed by the editor as SPAM
May 30, 2012 at 3:24 am

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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, MVP, M.Sc (Comp Sci)
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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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, MVP, M.Sc (Comp Sci)
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

seven.srikanth Ten Centuries Points: 1212 More actions · Answer 3

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.

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 4

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.

seven.srikanth Ten Centuries Points: 1212 More actions · Answer 5

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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, MVP, M.Sc (Comp Sci)
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

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 7

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

prettsons (5/30/2012)
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.

I would suggest not guessing solutions until we know the extent of the damage, which we don't. As a recovery expert you must know the importance of diagnosing the problem before prescribing solutions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Lowell SSC Guru Points: 323518 More actions · Answer 9

If I was ever dropped into this kind of situation, I'd hire Gail as a consultant - by - remote in a heartbeat, even if the money came out of my own pocket.

If any of you have ever followed any of the many corruption posts here on SSC, you'd see the breadth of her knowledge would save you tons of money just by the amount of downtime she would minimize.

I'm just sayin.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

adb2303 SSCertifiable Points: 7588 More actions · Answer 10

Gail - have you got a second SSC account called "Lowell"? 😉

laskozinin SSC Journeyman Points: 77 More actions · Answer 11

This will only work if the SQL Server was stopped when you copied the MDF file from the old SQL Server. If you copied the file while the SQL Server was running this file probably will not attach either and you will need to get a valid backup of the database. Download help tool for sql database - SQL Server Restore Toolbox. http://www.sqlserver.restoretools.com/[/url] You can read up more on SQL Server here. http://www.filerepairforum.com/forum/microsoft/microsoft-aa/sql-server/498-creating-a-new-database-using-an-mdf-file?_=1416149856104