很多人在接手数据库服务器的时候第一眼都会被那一堆 .mdf、.ldf、.ndf 文件搞懵。盘上明明躺着几十个G的文件却分不清哪个是“本体”、哪个是“日志”更别提遇到磁盘快满、想把库换个目录、或者需要从旧服务器把库拷到新服务器的时候到底该动哪个文件、能不能直接复制、不能的话又该怎么弄。这篇文章就围绕 SQL Server 的三种数据库文件把 mdf/ldf/ndf 各自扮演什么角色、添加数据文件的方法、分离与附加数据库的完整流程以及我在实际运维中踩过的坑一次说清楚。无论是刚入门的开发还是被临时拉去救火的运维都可以照着做。1. 三种数据库文件的分工MDF、LDF、NDF各管什么1.1 主数据文件MDF是数据库的“户口本”MDF 是 Master Data File 的缩写每个数据库有且只有一个主数据文件。它不光是存储业务数据的地方更重要的是保存了数据库的系统元数据——这个库有哪些表、索引、文件组、分区方案这些信息全都放在 MDF 里。可以把它理解为数据库的“户口本”记录着整个库的来龙去脉。如果你从来没有手动添加过额外的数据文件那么你在 SQL Server 里新建的所有表、写入的所有数据默认都会落在 MDF 里面。也就是说一个中小型数据库往往只有一个 MDF 和一个 LDF看不见 NDF 非常正常它不是必备文件。有一点容易引起误会MDF 的扩展名并不是 SQL Server 识别文件身份的唯一依据。SQL Server 在附加数据库时实际上是读取 MDF 文件头里记录的数据库标识和文件组信息扩展名就算改成别的也能识别。但是我很不建议大家改扩展名团队协作时一个非标准的后缀会制造大量不必要的困惑系统工具和第三方脚本也大都按 .mdf/.ldf/.ndf 来约定判断。1.2 日志文件LDF是数据库的“黑匣子”LDF 是 Log Data File 的缩写也叫事务日志文件。很多人以为它只是“记录操作日志没啥用”这是一个很危险的理解。SQL Server 使用预写日志Write-Ahead Logging机制任何一个修改操作比如 UPDATE、DELETE甚至创建索引都必须先把日志写入 LDF然后再把数据页真正写到 MDF 里。这个机制的直接好处是数据库如果突然断电或者系统崩溃重启后 SQL Server 会读 LDF把未提交的事务回滚UNDO把已提交但还没落盘的数据重新应用REDO保证数据库处于一致状态。LDF 就是崩溃恢复时的“黑匣子”。LDF 的另一个不为人注意的角色是它占了磁盘空间但不一定小。如果恢复模式是“完整”或“大容量日志”而你又从来没有做过事务日志备份LDF 会持续膨胀直到把磁盘塞满。我处理过的最夸张的案例里一个库的 LDF 达到了 600GB而 MDF 本身只有 30GB。日志文件的维护是 SQL Server 运维里最容易翻车的地方后面我会专门讲怎么处理。1.3 次要数据文件NDF是数据库的“增援部队”NDF 是 Secondary Data File 的缩写也就是次要数据文件。一个数据库可以一个 NDF 都没有也可以建很多个。NDF 和 MDF 在角色上有区别但存数据的能力完全一样差异在于数据库的元数据、系统表始终在主文件中业务数据则可以分散到各个 NDF。什么时候需要 NDF最常见的两个场景是容量分摊和 IO 分流。容量分摊很好理解单块磁盘空间不够了把新增数据放到另一块磁盘上的 NDF 里。IO 分流则是把不同文件的读写分散到不同的物理磁盘上减少单块磁盘的负载压力。对高并发系统来说把数据文件、日志文件分开放在不同磁盘对提升响应速度有明显帮助。用一个表把这三种文件的角色对比一下对比项MDF主数据文件NDF次要数据文件LDF事务日志文件英文全称Master Data FileSecondary Data FileLog Data File数量要求每个数据库必须且只能有 1 个0 到多个至少 1 个存储内容系统元数据 业务数据业务数据、索引事务日志记录主要用途数据库文件体系的入口扩容、IO 分流、文件组分区崩溃恢复、事务回滚默认扩展名.mdf.ndf.ldf一个容易被忽略的细节是事务日志文件尽量不要和重要的数据文件放在同一块机械硬盘上。数据文件频繁读日志文件频繁写两块流争抢同一个磁头会让整体 IO 性能明显下滑。SSD 上这个问题被大大弱化但生产环境我依然建议日志单独放一块盘。2. 文件在磁盘上的位置、能不改名直接挪吗移动数据库文件的正确姿势2.1 默认路径和文件命名规律默认安装 SQL Server 后数据库文件通常会放在这个目录下C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA这里面能看到很多系统数据库文件比如 master.mdf、model.mdf、msdb.mdf还有你新建用户数据库时自动生成的文件。假如你新建一个名叫 TestDB 的数据库默认生成的物理文件会是 TestDB.mdf 和 TestDB_log.ldf。想精确查看某个数据库包含哪些文件、逻辑名称和物理路径用下面的查询最直接SELECT DB_NAME(f.database_id) AS database_name, f.type_desc, f.name AS logical_name, f.physical_name, f.size * 8 / 1024 AS size_mb, f.max_size, f.growth FROM sys.master_files f WHERE DB_NAME(f.database_id) NTestDB;这条语句会返回数据库的所有文件信息包括逻辑文件名例如 TestDB、TestDB_log、物理路径、当前大小、最大大小和增长方式。它是文件运维里最常用的查询之一排查问题时建议先跑一遍。2.2 千万不能直接复制或剪切在用的数据库文件经常有人问“能不能直接把 MDF 文件复制到U盘当备份”答案是数据库在线运行时不能。SQL Server 对数据库文件是独占打开的直接复制会报“文件正由另一进程使用”。那停掉 SQL Server 服务再复制呢可以但这相当于对整个实例做“冷备份”期间所有数据库全部不可用对生产环境来说代价太大。最危险的操作是直接在文件资源管理器里对数据库文件做剪切、重命名或删除这轻则导致该库启动失败重则损坏文件。如果你确实要把数据库文件移动到另一个目录只有两条正规路线分离数据库 → 移动文件 → 附加数据库。适合整个库换目录的场景。用 ALTER DATABASE ... MODIFY FILE 修改文件路径然后把数据库脱机物理移动文件再重新联机。适合文件很大、分离附加太重的场景。第二种方式的参考脚本USE [master]; ALTER DATABASE [TestDB] MODIFY FILE ( NAME NTestDB, FILENAME NE:\SQLData\TestDB.mdf );执行后 SQL Server 会知道 TestDB.mdf 的新位置但物理文件还得你自己移动。接着把数据库脱机、移动文件、再联机ALTER DATABASE [TestDB] SET OFFLINE; -- 这里在操作系统层面移动文件 ALTER DATABASE [TestDB] SET ONLINE;这段脚本的数据库不可用时间基本等于移动文件所需的时间。对几百GB的库来说比分离附加要温和不少。3. 给SQL Server添加数据文件什么时候加、怎么加更合理3.1 判断是否真的需要添加 NDF先给一个反直觉的结论不是数据库一“满了”就该添加 NDF。如果只是 MDF 当前文件达到设置的最大值先检查磁盘有没有剩余空间有的话直接调大现有文件的 MAXSIZE 或者初始大小更省事。因为新增 NDF 后已有的表不会自动把数据搬过去你还需要额外考虑数据迁移。真正适合添加 NDF 的场景有几个数据库需要把不同表甚至不同分区放到不同文件组比如把历史数据放入独立的只读文件组便于单独备份或归档。数据量超大单文件或单块磁盘已经无法满足容量要求。业务出现明显的 IO 瓶颈希望把部分对象放在另一块更快的磁盘上。要对现有文件的使用空间有数可以用这条命令看文件组统计DBCC SQLPERF(FILEGROUPSTATS);再配合 sys.master_files 里的大小信息基本就能判断是扩容还是新增文件了。3.2 SSMS 图形界面添加数据文件的步骤用 SSMSSQL Server Management Studio操作最直观步骤也不复杂对象资源管理器中右键目标数据库选择“属性”。左侧选择“文件”页。点击右下角的“添加”按钮。在列表里填写逻辑名称比如 TestDB_Data2文件类型保持“行数据”文件组可以根据需要选择 PRIMARY 或新建文件组。设置初始大小。不要给得过小比如一个现有文件 30GB 的库新文件初始化给 1GB 就很不合理很快又要自增会产生大量 IO 抖动。给 10GB、20GB 甚至与现有文件持平都可以。设置自动增长。文件增长建议选“按 MB”并且给一个固定值比如 2048MB。用百分比在文件体积很大的时候会导致单次增长异常剧烈不推荐。路径选到目标磁盘点击确定。一个很多人不知道的细节同一个文件组里如果已经有文件SQL Server 并不是永远优先写新文件而是会在文件组内的多个文件之间按一定比例均衡填充。所以添加新文件后MDF 的写入负载通常会很快被分摊一部分但这并不代表旧表的数据会自动迁移到新文件。想让特定表的数据落到新文件组必须重建表的聚集索引或者创建索引时指定文件组。3.3 T-SQL 脚本添加文件生产环境更推荐的方式生产环境或者批量部署时用 SSMS 点界面太慢也不方便记录变更。T-SQL 脚本更适合。向现有 PRIMARY 文件组添加 NDFALTER DATABASE [TestDB] ADD FILE ( NAME NTestDB_Data2, FILENAME ESQLData\TestDB_Data2.ndf, SIZE 20GB, MAXSIZE 100GB, FILEGROWTH 2GB );如果希望新建一个文件组并把文件放进去可以分两步ALTER DATABASE [TestDB] ADD FILEGROUP [FG_Archive]; ALTER DATABASE [TestDB] ADD FILE ( NAME NTestDB_Archive, FILENAME ESQLData\TestDB_Archive.ndf, SIZE 20GB, MAXSIZE 100GB, FILEGROWTH 2GB ) TO FILEGROUP [FG_Archive];这里有个细节值得强调SIZE、MAXSIZE、FILEGROWTH 不写默认单位是 MB但可以直接写 GB 或者 MB 来指定单位。MAXSIZE 我强烈建议显式设置防止文件在无人值守时无限增长最终把磁盘塞满。设置了 MAXSIZE 后即便文件达到上限数据库报错你也能快速反应过来而不是让整块磁盘和同实例的其他库一起遭殃。加完文件组之后如果想充分利用新的文件组还需要把对象迁移过去。比如把某张表移到 FG_Archive 文件组重建聚集索引是最常用的方式CREATE CLUSTERED INDEX CX_Orders_OrderID ON dbo.Orders(OrderID) WITH (DROP_EXISTING ON) ON [FG_Archive];但要注意如果表上有大量非聚集索引这个操作会造成不小的 IO 和日志开销最好放在维护窗口执行。4. 分离和附加数据库从原理到操作的完整链路4.1 分离Detach是什么分离前必须准备什么分离操作可以理解成让 SQL Server 实例不再管理这个数据库释放所有数据库文件的句柄。分离成功后数据库在实例里消失但磁盘上的 MDF/LDF/NDF 文件完好无损内容也没变。之后这些文件就可以被移动、复制或打包带走了。分离前必须确认三件事没有活动连接。有用户在连着分离会直接失败。没有正在进行的备份、还原、DBCC CHECKDB 等任务。数据库没有启用复制、日志传送、数据库镜像或 Always On 可用性组。如果有连接正在占用先把数据库切到单用户模式并回滚所有未提交事务USE [master]; ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;这个语句会强制断开所有会话并回滚事务然后把库设为单用户状态接下来就可以执行分离了。SSMS 图形界面操作路径右键数据库 → 任务 → 分离。弹窗里的“更新统计信息”建议勾选。这样附加到新环境后优化器拿到的统计信息是新鲜的查询计划不容易因为统计信息缺失而跑偏。“保留全文目录”按默认勾选即可。4.2 附加Attach数据库的标准操作附加是分离的逆过程。SQL Server 打开 MDF读取文件头中的数据库标识然后根据内部记录去找对应的 LDF 和 NDF。如果所有文件都齐全直接就挂载完成如果日志文件缺失会走特殊的重建流程这部分后面单独讲。图形界面操作很简单右键“数据库”节点 → 附加 → 添加 → 选择 .mdf 文件 → 在下面的文件列表中确认 LDF 和 NDF 的路径是否正确 → 确定。T-SQL 方式推荐用 CREATE DATABASE ... FOR ATTACH 语法CREATE DATABASE [TestDB] ON ( FILENAME ND:\DBFiles\TestDB.mdf ), ( FILENAME ND:\DBFiles\TestDB_log.ldf ) FOR ATTACH;如果数据库原本有多个 NDF也要在 ON 子句中全部列出来否则附加时会因为找不到文件而失败。这里要特别提示一下早期老教程里教的 sp_attach_db 存储过程已经被微软标记为“已弃用”新脚本不要再用了。所有场景统一用 CREATE DATABASE ... FOR ATTACH 更标准。4.3 附加最常见的三个坑权限、版本、文件损坏第一个坑是权限。SQL Server 是以服务账户身份访问文件系统的不是以你当前登录的 Windows 用户身份。如果你附加的文件存放在某个新目录而该目录没有给 SQL Server 服务账户授予访问权限就会报“无法检索文件的属性”或“操作系统错误 5拒绝访问”。解决办法是在文件夹属性里给 SQL Server 服务账户授予读取和执行权限。通常加一个 Authenticated Users 的读取权限也能临时解决但按最小权限原则建议只给对应服务账户授权。第二个坑是版本兼容。MDF 文件格式和 SQL Server 版本强绑定。用 2019 实例去附加一个 2022 实例生成的数据库文件大多数情况会报类似“数据库版本为 967服务器支持部分为 904”的错误注意 904 是 SQL Server 2019 的版本号967 是 2022 的。出现这个报错唯一的正规路线是在高版本实例上把库备份出来再还原到低版本实例或者用导入导出工具迁移数据。直接改文件头版本号这种事我强烈不建议做风险大到无法预料。第三个坑是文件损坏。数据库经历过突然断电、存储故障后再拿到的 MDF附加时可能报“文件头无效”。不要反复拿同一份文件重试先复制一份副本然后尝试 DBCC CHECKDBDBCC CHECKDB(NTestDB) WITH NO_INFOMSGS, ALL_ERRORMSGS;如果数据库本身已经损坏这个命令也可能跑不完。这种时候能救数据的最可靠方案是结合事务日志备份做基于时间点的还原单纯靠一个 MDF 文件救库成功率并不高。这也是为什么我一直强调附加这种操作不能替代备份。5. 实战中绕不开的三个典型案例只有MDF怎么救、日志膨胀怎么压、分离失败怎么解除5.1 只有 MDF 文件如何重建 LDF 并附加我遇到过几次这样的情况同事从旧服务器把文件拷出来结果 LDF 漏拷了或者日志文件在传输过程中损坏手里只剩下一个 MDF。这种情况下可以尝试让 SQL Server 自动重建日志文件CREATE DATABASE [TestDB] ON ( FILENAME ND:\DBFiles\TestDB.mdf ) FOR ATTACH_REBUILD_LOG;这个命令会根据 MDF 里的信息创建一个全新的日志文件。但有个前提原数据库在分离或关闭时处于“干净”状态没有未提交事务需要回滚。如果 LDF 丢失时数据库里还有未提交事务重建的日志无法执行完整的 UNDO附加后数据库可能处于异常状态。所以执行这条命令前最好对 MDF 先复制一份副本再操作。如果附加失败不要再对原始文件做任何写操作留给后续更高阶的数据修复手段处理。如果数据库原本还有多个 NDF也可以在 ON 子句中一并列出CREATE DATABASE [TestDB] ON ( FILENAME ND:\DBFiles\TestDB.mdf ), ( FILENAME ND:\DBFiles\TestDB_Data2.ndf ) FOR ATTACH_REBUILD_LOG;这样 SQL Server 会拉起主数据文件和次要数据文件并重建缺失的日志。5.2 LDF 日志文件膨胀到磁盘快满怎么办这个问题太常遇到了单独拿出来说。日志膨胀的前提基本是两种数据库恢复模式是“完整”但从来没有做过事务日志备份或者存在一个长期不提交的事务导致日志无法截断。前者更常见。处理顺序不能乱否则你会白忙活甚至把情况弄得更糟。第一步先看日志空间使用率DBCC SQLPERF(LOGSPACE);这个结果会显示每个数据库的日志文件大小、已用空间百分比。如果已用空间很高先查一下有没有长期未提交事务DBCC OPENTRAN;这个命令会展示最早的活动事务信息如果有结果说明有事务一直开着日志的截断始终被卡住。先找到对应会话让业务方提交或回滚事务问题才能解决。第二步根据恢复模式选择动作如果库并不需要严格的时间点恢复能力可以把恢复模式改成“简单”ALTER DATABASE [TestDB] SET RECOVERY SIMPLE;改成简单模式后SQL Server 会在检查点自动截断日志让日志空间可以被复用。如果库必须保持完整恢复模式那应该建立事务日志备份的例行任务。每次 BACKUP LOG 之后日志就有了截断点空间才能被释放BACKUP LOG [TestDB] TO DISK ND:\Backup\TestDB_log_backup.trn;第三步如果日志文件物理空间依然很大但内部已用空间很低才考虑收缩USE [TestDB]; DBCC SHRINKFILE (NTestDB_log, 1024);这里的 1024 表示目标大小 1024MB。但收缩日志只是“治标”如果不改变日志备份缺失的根因下一次大事务进来日志文件很快又会膨胀回去而且频繁收缩还会导致日志文件内部碎片化。真正的解法一定是先建立日志备份或改简单恢复模式。另外提醒一句千万不要在数据库还在运行的时候手动删除 .ldf 文件或者把它从目录里移走。这种操作等于在系统运行中拔掉日志记录数据库极大概率会进入质疑状态恢复过程极其痛苦。5.3 分离失败怎么正确解除阻塞并完成操作分离时最常见的报错是“无法分离数据库因为它正在使用中”。处理方法就是前面提到的单用户模式强制踢会话。完整的一套流程是这样USE [master]; ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; EXEC master.dbo.sp_detach_db dbname NTestDB;如果当前库里的数据很重要在强制踢会话前最好先做一次备份尤其是当系统里疑似有跑了一半的批量任务时。ROLLBACK IMMEDIATE 会立即回滚所有未提交事务大事务的回滚本身也要花时间如果回滚过程被打断事务会再进入恢复流程。还有一类分离失败的原因是数据库配置了高可用特性。比如数据库属于 Always On 可用性组普通分离根本不可用需要先从可用性组中移除数据库再分离。数据库镜像关系中的镜像端也不能直接分离。遇到这种情况先确认高可用状态再动手。我在实际运维里处理文件相关操作时最依赖的习惯是动文件之前一定先备份宁可多花几分钟做备份也不要为省这几分钟把自己的数据置于危险中。很多人第一次搞附加、分离、移动文件时总觉得自己操作过程没问题结果因为一个小疏漏把一个好端端的库搞进“质疑”状态那时候再想救回来成本和风险都完全不一样了。