Attach Database failed for server 'M3'

  • deba_20032004

    Default port

    Points: 1476

    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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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: 1004424

    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: 1152

    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: 714708

    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: 1152

    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: 1004424

    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
  • This was removed by the editor as SPAM

  • Schadenfreude-Mei

    SSChampion

    Points: 14083

    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

  • This was removed by the editor as SPAM

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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: 323354

    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: 7586

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

  • laskozinin

    SSC Journeyman

    Points: 77

    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

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

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