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

Question

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?

Answer 1

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)

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

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