Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
524 views
in Technique[技术] by (71.8m points)

ssms - SQL Server: How to attach / repair a detached / damaged database?

A database server crashed. I was able to get the mdf and the log file and I am trying to mount it on another server. I don't have backup as it was a development database.

When I try to attach the mdf and ldf files, Sql Server Management Studio give me this error:

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

    Attach database failed for Server

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

    Could not redo log record (457:14202:19), for transaction ID (0:478674), on page (1:519205), database 'WSS_Content_5555' (database ID 15). Page: LSN = (370:463:113), type = 1. Log: OpCode = 2, context 2, PrevPageLSN: (298:40524:64).
    Restore from a backup of the database, or repair the database.
    During redoing of a logged operation in database 'WSS_Content_5555', an error occurred at log record ID (457:14202:19).
    Typically, the specific failure is previously logged as an error in the Windows Event Log service.
    Restore the database from a full backup, or repair the database.
    Could not open new database 'WSS_Content_5555'.
    CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 3456)

I don't know how to repair the database. Does it need to be attached before beeing repaired? In that case, how can I attach it?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

You can try a workaround. In short:

  1. Create a dummy DB with the same name (may need to remove the real DB first, save the original files or rename then).
  2. Take the dummy off line (detach and (set offline or stop the SQL service)).
  3. Delete dummy files, replace then with the real DB files.
  4. Try to re-attach de DB

Edit

As by OP comment note you also can need to rebuild the log (if you lost transactions)

  1. ALTER DATABASE [MyDatabase ] REBUILD LOG ON (NAME=’MyDatabaseLog’,FILENAME=’D:Microsoft SQL ServerYourDataPathDataLogfile.ldf’)

    and put the DB in multiple users log (taking the DB off can require you to put it in single use mode)

  2. ALTER DATABASE [nomdb] SET MULTI_USER

For all the gore details you can refer to the Paul Randal Article

(Note in this article the author uses EMERGENCY MODE to (attempt) repair the transaction log)

I already used it with success but depending on the extent of the damage or others details it can be a impossible task. Consider restoring a backup.

Note this stunts are fine in a development server but you really need to plan (and drill) for disaster recovery in a prodution server.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...