如何从SQL Server 2005中的MDF恢复数据库？

I have an MDF file and no LDF files for a database created in MS SQL Server 2005. When I try to attach the MDF file to a different SQL Server, I get the following error message.

我有一个MDF文件，没有在MS SQL Server 2005中创建的数据库的LDF文件。当我尝试将MDF文件附加到其他SQL Server时，我收到以下错误消息。

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

无法重建日志，因为数据库关闭时没有打开的事务/用户，数据库没有检查点，或者数据库是只读的。如果由于硬件或环境故障手动删除或丢失事务日志文件，则可能发生此错误。

I would like to accomplish any one of the following options:

我想完成以下任何一个选项：

Attach the database without data loss (unlikely but would save me some time).
附加数据库没有数据丢失（不太可能，但会节省我一些时间）。
Attach the database with data loss (whatever transactions were open are lost).
附加数据库丢失数据（无论打开的事务是否丢失）。
Recover the schema only (no data) from the MDF file.
仅从MDF文件中恢复架构（无数据）。

What SQL commands can I try to get my database going again?

我可以尝试使用哪些SQL命令来重新启动数据库？

8 个解决方案

#1

I found the following document on Experts Exchange.

我在专家交流中找到了以下文件。

patrikt: You will have data loss but it can be done.

patrikt：您将丢失数据，但可以完成。

1. Detach database and move your mdf to save location.
2. Create new databse of same name, same files, same file location and same file size.
3. Stop SQL server.
4. Swap mdf file of just created DB to your save one.
5. Start SQL. DB will go suspect.
6. ALTER DATABASE yourdb SET EMERGENCY
7. ALTER DATABASE yourdb SET SINGLE_USER
8. DBCC CHECKDB (yourdb, REPAIR_ALLOW_DATA_LOSS)
9. ALTER DATABASE yourdb SET MULTI_USER
10. ALTER DATABASE yourdb SET ONLINE

#2

Here are details that cover parts 2) and 3) in case re-creating log doesn’t work which can happen if MDF file is corrupted.

以下是部分2）和3）的详细信息，以防重新创建日志不起作用，如果MDF文件已损坏，则可能发生这种情况。

You can recover data and structure only by reading MDF file with some third party tool that can de-code what’s written as binary data but even with such tools you can’t always do the job completely.

您只能通过使用某些第三方工具读取MDF文件来恢复数据和结构，该工具可以对写入二进制数据的内容进行解码，但即使使用此类工具，也无法始终完全完成工作。

In such cases you can try ApexSQL Recover. From what I know this is the only tool that can do this kind of job but it’s quite expensive.

在这种情况下，您可以尝试ApexSQL Recover。据我所知，这是唯一可以做这种工作的工具，但它非常昂贵。

Much better idea is to try to recover these from any old backups if you have any.

更好的想法是尝试从任何旧备份恢复这些，如果你有任何。

#3

FROM a post at SQL Server Forums Attaching MDF without LDF:

来自SQL Server论坛的帖子在没有LDF的情况下附加MDF：

If you want to attach a MDF without LDF you can follow the steps below It is tested and working fine

如果要连接不带LDF的MDF，可以按照以下步骤进行测试并正常工作

Create a new database with the same name and same MDF and LDF files

创建具有相同名称和相同MDF和LDF文件的新数据库
Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.

停止sql server并将现有MDF重命名为新MDF并将原始MDF复制到此位置并删除LDF文件。
Start SQL Server

启动SQL Server
Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

现在您的数据库将被标记为可疑5.更新sysdatabases以更新为紧急模式。这不会在启动时使用LOG文件

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

Restart sql server. now the database will be in emergency mode

重启sql server。现在数据库将处于紧急模式
Now execute the undocumented DBCC to create a log file

现在执行未记录的DBCC以创建日志文件

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') -- Undocumented step to create a new log file.

DBCC REBUILD_LOG（dbname，'c：\ dbname.ldf'） - 未记录的步骤以创建新的日志文件。

(replace the dbname and log file name based on ur requirement)

（根据您的要求替换dbname和日志文件名）

Execute sp_resetstatus

执行sp_resetstatus
Restart SQL server and see the database is online.

重新启动SQL Server并查看数据库是否在线。

UPDATE: DBCC REBUILD_LOG does not existing SQL2005 and above. This should work:

更新：DBCC REBUILD_LOG不存在SQL2005及更高版本。这应该工作：

USE [master]
GO
CREATE DATABASE [Test] ON 
    (FILENAME = N'C:\MSSQL\Data\Test.mdf')
    FOR ATTACH_REBUILD_LOG
GO

#4

have you tried to ignore the ldf and just attach the mdf:

你试图忽略ldf并只是附加mdf：

sp_attach_single_file_db [ @dbname = ] 'dbname' , [ @physname = ] 'physical_name'

sp_attach_single_file_db [@dbname =]'dbname'，[@ physname =]'physical_name'

i don't know exactly what will happen to your open transactions (probably just lost), but it might get your data back online.

我不确切知道您的开放交易会发生什么（可能只是丢失了），但它可能会使您的数据重新上线。

-don

#5

See here : Rebuild master and restore system databases from complete disk failure which has a very nice explanation

请参阅此处：从完整磁盘故障重建主系统和恢复系统数据库，这有一个非常好的解释

#6

Just had this problem myself, but none of the above answers worked for me.

我自己也遇到过这个问题，但上述答案都没有对我有用。

But instead, I found this which worked a treat and so I thought I'd share this for everyone else:

但相反，我发现这个有用，所以我想我会为其他人分享这个：

http://www.kodyaz.com/articles/sql-server-attach-database-mdf-file.aspx

#7

Found a another way that works completely:

找到另一种完全有效的方法：

Create new database with same name to default database location.
创建具有相同名称的新数据库到默认数据库位置。
Stop SQL server.
停止SQL服务器。
Copy old mdf file to overwrite newly created mdf file and delete new ldf file
复制旧的mdf文件以覆盖新创建的mdf文件并删除新的ldf文件
Start SQL Server, database will be in emergency mode
启动SQL Server，数据库将处于紧急模式
Detach the emergency mode database
分离紧急模式数据库
Copy original ldf file to default database location (where new LDF file as created and deleted under step 3 above.
将原始ldf文件复制到默认数据库位置（在上面的步骤3中创建和删除新LDF文件的位置）。
Attach the database MDF file.
附加数据库MDF文件。

I got a working database after trying all of the above that failed for me.

在尝试了以上所有失败后，我得到了一个工作数据库。

#8

I hope it is easy to do so,

我希望这样做很容易，

Open SQL Server
打开SQL Server
Click New Query
单击“新查询”
Execute the following query

执行以下查询

sp_attach_single_file_db @dbname='dbname',@physname='C:\Database\dbname.MDF'

sp_attach_single_file_db @dbname ='dbname'，@ physname ='C：\ Database \ dbname.MDF'

Where dbname is you want to show in Object Explorer, where @physname is the local filepath location of your mdf file.

其中dbname是要在对象资源管理器中显示的位置，其中@physname是mdf文件的本地文件路径位置。

Hope it will help someone, i done by above, got both structure and also data.

希望它能帮助某人，我完成上面的工作，得到结构和数据。

Tested in Sql Server 2000 and 2008. In Sql Server 2000 it is not working, but works perfectly in 2008.

在Sql Server 2000和2008中测试过。在Sql Server 2000中，它无法正常工作，但在2008年完美运行。

#1