sql-server - 尝试将 mdf 文件附加到 localDb 会引发错误，至少需要一个文件

Question

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server '(localdb)\mssqllocaldb'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

At least one file is needed for Database Attach. (Microsoft.SqlServer.Smo)

我正在尝试将此 .mdf 数据库文件附加到我的 LocalDb 实例。如果我也能将其连接到 SQL Server 就好了。我在同一目录中有 .ldf 文件

Answer 1

为了完成 - Jim's comment解决(一半)问题并让你继续前进。

问题的另一“一半”是 - 如果您最终想要重命名物理数据库文件怎么办？答案就在这个CodeProject post .

<小时/>

步骤:

1. ALTER DATABASE设置新的物理文件名(数据文件和日志文件)
   ^{只有重新启动 SQL Server 或数据库脱机并恢复联机后才会生效}

   • ALTER DATABASE [CurrentName] MODIFY FILE (NAME = 'CurrentName', FILENAME = '<Full-Path-Required>\NewDbName.mdf');
   • ALTER DATABASE [CurrentName] MODIFY FILE (NAME = 'CurrentName_log', FILENAME = '<Full-Path-Required>\NewDbName_log.ldf');

2. ALTER DATABASE再次设置新的逻辑文件名(同样是数据和日志文件)
   ^立即生效

   • ALTER DATABASE [CurrentName] MODIFY FILE (NAME = 'CurrentName', NEWNAME = 'NewDbName');
   • ALTER DATABASE [CurrentName] MODIFY FILE (NAME = 'CurrentName_log', NEWNAME = 'NewDbName_log');

3. 脱机并恢复联机或重新启动 SQL Server

   • 使用 SQL Server Management Studio:
     1. 右键单击重命名的数据库，然后单击 Take Offline下Tasks .
     2. 右键单击(离线)数据库，然后单击 Bring Online下Tasks .
   • 使用 T-SQL:
     1. ALTER DATABASE [CurrentName] SET OFFLINE WITH ROLLBACK IMMEDIATE; (将其设置为离线并断开所有客户端的连接)
     2. ALTER DATABASE [CurrentName] SET ONLINE;

<小时/>

完整代码:

-- Find "CurrentName" (without quotes) and replace with the current database name
-- Find "NewDbName" (without quotes) and replace with the new database name


USE [CurrentName];

-- Change physical file names:
ALTER DATABASE [CurrentName] MODIFY FILE (NAME = 'CurrentName', FILENAME = '<Full-Path-Required>\NewDbName.mdf');
ALTER DATABASE [CurrentName] MODIFY FILE (NAME = 'CurrentName_log', FILENAME = '<Full-Path-Required>\NewDbName_log.ldf');

-- Change logical names:
ALTER DATABASE [CurrentName] MODIFY FILE (NAME = 'CurrentName', NEWNAME = 'NewDbName');
ALTER DATABASE [CurrentName] MODIFY FILE (NAME = 'CurrentName_log', NEWNAME = 'NewDbName_log');

-- Take offline and back online
USE [master]
GO
ALTER DATABASE [CurrentName] SET OFFLINE WITH ROLLBACK IMMEDIATE;
-- Then navigate to <Full-Path-Required> and rename the files
ALTER DATABASE [CurrentName] SET ONLINE;