SQL Server:如何附加/修复分离/损坏的数据库? [英] SQL Server: How to attach / repair a detached / damaged database?

问题描述

数据库服务器崩溃.我能够获得 mdf 和日志文件，我正在尝试将它安装在另一台服务器上.我没有备份，因为它是一个开发数据库.

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.

当我尝试附加 mdf 和 ldf 文件时，Sql Server Management Studio 给我这个错误:

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?

推荐答案

您可以尝试一种解决方法.简而言之:

You can try a workaround. In short:

1. 创建一个同名的虚拟数据库(可能需要先删除真实数据库，保存原始文件或重命名).
2. 使虚拟离线(分离并(设置离线或停止 SQL 服务)).
3. 删除虚拟文件，然后用真实的数据库文件替换.
4. 尝试重新附加数据库

编辑

根据 OP 注释说明，您还可能需要重建日志(如果丢失事务)

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 Server\YourDataPath\Data\Logfile.ldf')

1. ALTER DATABASE [MyDatabase ] REBUILD LOG ON (NAME=’MyDatabaseLog’,FILENAME=’D:\Microsoft SQL Server\YourDataPath\Data\Logfile.ldf’)

并将数据库放在多用户日志中(取下数据库可能需要您将其置于单次使用模式)

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

ALTER DATABASE [nomdb] SET MULTI_USER

ALTER DATABASE [nomdb] SET MULTI_USER

有关所有血腥细节，您可以参考 保罗兰德尔文章

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

(注意本文作者使用 EMERGENCY MODE 来(尝试)修复事务日志)

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

这篇关于SQL Server:如何附加/修复分离/损坏的数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！