如何在没有LDF文件的情况下附加数据库？

How can I attach a database without an LDF file in SQL Server?

如何在SQL Server中附加没有LDF文件的数据库？

8 个解决方案

#1

You can use sp_attach_single_file_db to attach a database which is missing it's log file.

您可以使用sp_attach_single_file_db附加缺少日志文件的数据库。

#2

You can try what is posted here by MohammedU. Basically, what he uses the DBCC REBUILD_LOG command. It will work depending on the version of your server.

你可以试试MohammedU在这里发布的内容。基本上，他使用DBCC REBUILD_LOG命令。它将根据您的服务器版本而有效。

Here are the steps (without details):

以下是步骤（没有详细信息）：

1. Rename existing .mdf file to .mdf_old
2. 将现有.mdf文件重命名为.mdf_old
3. Create a new database with same .mdf and .ldf file as old one.
4. 使用与旧文件相同的.mdf和.ldf文件创建新数据库。
5. Stop the sql server
6. 停止sql server
7. Rename .mdf and .ldf files of the new db to .mdf_old and .ldf_old
8. 将新数据库的.mdf和.ldf文件重命名为.mdf_old和.ldf_old
9. Rename .mdf_old to .mdf
10. 将.mdf_old重命名为.mdf
11. Start sql server
12. 启动sql server
13. You should see db in suspect mode
14. 您应该看到db处于可疑模式
15. Change the database context to Master and allow updates to system tables
16. 将数据库上下文更改为Master并允许更新系统表
17. Set the database in Emergency (bypass recovery) mode.
18. 将数据库设置为紧急（旁路恢复）模式。
19. Stop and restart SQL server.
20. 停止并重新启动SQL Server。
21. Rebuild the log.
22. 重建日志。
23. Set the database in single-user mode and run DBCC CHECKDB to validate physical consistency.
24. 以单用户模式设置数据库并运行DBCC CHECKDB以验证物理一致性。
25. Turn off the updates to system tables.
26. 关闭系统表的更新。

#3

Try this steps through SQL Server Management Studio

通过SQL Server Management Studio尝试此步骤

1. Open SSMS and made a right click on databases.
2. 打开SSMS并右键单击数据库。
3. Select Attach option
4. 选择附加选项
5. Then click on add to attach MDF file.
6. 然后单击“添加”以附加MDF文件。
7. Select the file from the list and click on Ok
8. 从列表中选择文件，然后单击“确定”
9. Now the screen shows MDF file and LDF File(Not found)
10. 现在屏幕显示MDF文件和LDF文件（未找到）
11. Choose the LDF File and click on Remove option.
12. 选择LDF文件，然后单击“删除”选项。
13. After removing LDF File click on OK.
14. 删除LDF文件后，单击“确定”。
15. MDF file attached successfully in the databases list.
16. MDF文件已成功附加到数据库列表中。

#4

Try to attach it by adding the MDF file to the Attach Databases dialog. You'll note that the dialog will report the missing LDF file. Follow the steps as shown on the picture:

尝试通过将MDF文件添加到“附加数据库”对话框来附加它。您会注意到该对话框将报告缺少的LDF文件。按照图片所示的步骤操作：

#5

You can "just do it" it'll throw a warning that it couldn't find the .ldf, but it will still attach the db.

你可以“只做它”它会发出一个警告，它无法找到.ldf，但它仍会附加数据库。

#6

If you run into problems, verify that the mdf file is not read-only.

如果遇到问题，请验证mdf文件不是只读的。

#7

Here are Code Snippets to programaticaly create .ldf files

以下是programaticaly创建.ldf文件的代码片段

Following are 3 Methods.

以下是3种方法。

Method -1

方法-1

In my case I have my Database in DATA folder.

在我的情况下，我在DATA文件夹中有我的数据库。

You can get the full path to your Database by right clicking and then going to properties then you can copy the full path to your Database

您可以通过右键单击然后转到属性来获取数据库的完整路径，然后您可以将完整路径复制到数据库

As In my case path is as follows.

在我的情况下，路径如下。

C:\Program Files\Microsoft SQL Server\MSSQL11.DRIBBLEE\MSSQL\DATA

C：\ Program Files \ Microsoft SQL Server \ MSSQL11.DRIBBLEE \ MSSQL \ DATA

Now here is First Method by using store procedure(sp_attach_single_file_db) and passing it arguments(database name and physical path)

现在这里是第一个方法，使用存储过程（sp_attach_single_file_db）并传递它的参数（数据库名称和物理路径）

 USE [master]
 GO

EXECUTE sp_attach_single_file_db 
@dbname='AdventureWorksDW_2012',
@physname=N'C:\ProgramFiles\MicrosoftSQLServer\MSSQL11.DRIBBLEE\MSSQL\DATA\AdventureWorksDW2012_Data.mdf'

GO

execute the code you after executing the code go to your database folder where it resides you will see .ldf file created over there.

执行代码后执行代码转到它所在的数据库文件夹，你会看到在那里创建的.ldf文件。

However you will get following message in your

但是，您将收到以下消息

The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_log.ldf" may be incorrect.
New log file 'C:\Program Files\Microsoft SQL Server\MSSQL11.DRIBBLEE\MSSQL\DATA\AdventureWorksDW_2012_log.ldf' was created.

Now you can attach your database and after Attaching the Database right click at your server name in Object Explorer and refresh.

现在，您可以附加数据库，并在“附加数据库”后右键单击对象资源管理器中的服务器名称并刷新。

Method-2

方法2

IF your database have one or more log files missing you can use following

如果您的数据库缺少一个或多个日志文件，则可以使用以下内容

 CREATE DATABASE db_namehere ON
 (
    FILENAME=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_namehere.mdf')

FOR ATTACH_REBUILD_LOG
GO

Method-3

方法-3

If you database has only one log file missig you can use this

如果您的数据库只有一个日志文件missig，您可以使用它

 CREATE DATABASE db_name ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH
GO

Further you can read in BOOKs Online to get more information.

您还可以在线阅读以获取更多信息。

#8

EXEC sp_attach_single_file_db @dbname = 'DBNAME',@physname = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DBNAME_Data.mdf';

I tried and it worked... Hopefully this helps.

我尝试过它有效...希望这会有所帮助。