搞SQL Server的几乎都会碰上这么一件事别人丢给你一个mdf文件要你把数据库挂起来或者某天磁盘满了让你给数据库加个ndf文件分担压力再或者要从测试机迁移到生产机需要先分离再附加数据库。mdf、ldf、ndf这三个后缀表面看只是数据库文件的称呼实际上牵扯到SQL Server的存储架构、日志机制、文件组设计这些比较底层的东西理解对了操作时才知道自己在做什么。这篇文章就把三者的区别、如何给SQL Server添加数据文件、分离和附加数据库的具体操作和常见坑整理一遍适合刚接触SQL Server的开发、运维以及准备做数据库迁移但还没有完整方案的朋友。1. 文件体系拆解mdf、ldf、ndf各管什么活1.1 主数据文件mdf数据库的“地基”mdf全称Primary Data File是数据库的主数据文件。每个数据库有且只有一个mdf它存放两类东西一类是用户数据也就是表、索引、存储过程等对象另一类是数据库的启动信息和系统对象包括表的元数据、分配位图等。SQL Server启动并定位一个数据库时首先就要打开mdf文件读取里面的元数据才能知道数据库里有哪些对象、文件组和日志文件。对SQL Server的存储机制要有基本概念数据按8KB的“页”存储8个连续的页组成一个“区”也就是64KB。mdf文件从第9页开始前8页是文件头页、页空闲空间PFS、全局分配映射GAM、共享全局分配映射SGAM等特殊页用来管理文件内部空间的分配。这部分内容平时虽然用不到但明白这个结构就能理解为什么不要随便用文本编辑器打开mdf文件也不要试图手动修改文件内容——文件内部是二进制页组织不是普通文本破坏一个页可能导致整个数据库无法启动。1.2 事务日志文件ldf数据库的“账本”ldf全称Transaction Log File记录所有事务的操作日志。数据库每次写入数据之前都会先把日志写进ldf这叫Write-Ahead Logging预写日志。这样设计的好处是即使系统突然断电SQL Server也能通过日志把数据恢复到一致状态要么回滚未完成的事务要么前滚已经提交但还没来得及落盘的数据。日志文件和数据文件在物理结构上完全不同。数据文件按页组织日志文件按逻辑日志记录和虚拟日志文件VLF组织两者不能混着放。这也是为什么如果你在附加数据库时只给mdf不给ldfSQL Server会要求要么提供原有日志要么创建新日志后面会讲ATTACH_REBUILD_LOG。注意日志文件并不是越大越好。很多人看到ldf几十GB就开始担心其实只要定期做完整备份和日志备份ldf就会自动截断物理文件不一定缩小。真正要关注的是日志增长异常的情况比如长时间未做日志备份导致ldf无限膨胀撑爆磁盘。磁盘没空间时加多少ndf都没用因为日志也要写到磁盘上。1.3 次要数据文件ndf可多可少的扩展位ndf全称Secondary Data File是主数据文件之外的数据文件可以有多个。ndf和mdf在存储上没有什么本质区别都是存数据的物理文件只是mdf承担了系统元数据的功能而ndf不承担。所以你完全可以把用户表和索引放在ndfmdf只保留系统元数据。引入ndf主要是为了解决两个问题空间扩展和I/O分布。当主数据文件所在的磁盘分区快满了你可以把ndf放到另一个分区这样数据库总容量可以突破单块磁盘限制。在性能和扩展性上把压力较大的表放到性能更好的磁盘上也能得到实质收益。但要注意不是文件越多越好也不是文件组越复杂越好。对大多数中小数据库一个mdf加一个ndf或者干脆只有mdf完全够用。2. 给SQL Server添加数据文件什么时候加、怎么加最合理2.1 什么情况下该考虑添加ndf很多人一听到“数据库文件占用磁盘满了”第一反应是添加文件这是最常见的误用。在做这个操作之前至少先检查几个方面确认是不是日志文件ldf异常膨胀导致的磁盘满。如果是优先做日志备份或收缩日志而不是添加数据文件。确认表数据是否真的占了所有可用空间。可以在SSMS里右键数据库查看报告或者用sys.database_files查文件已用空间和未用空间。确认磁盘是否还有剩余空间。添加文件并不会缩小原有文件只是在另一个位置新增容量。如果是以下情况添加ndf是合理的主数据文件所在磁盘分区没有空间但另一个磁盘分区还有很大空间。你在做性能优化想把高并发访问的大表放到独立的文件组或更快的磁盘上。数据库文件数量太少需要拆分成多个文件以并行提升I/O吞吐。注意这在高端的存储环境下收益有限特别是SAN/SSD反而可能增加管理复杂度。2.2 图形界面方式SSMS里添加数据文件用SSMS给数据库添加数据文件是很多人第一次接触的操作流程不复杂但有些细节会影响后面的维护。打开SSMS连接到目标实例展开“数据库”找到目标库右键 - 属性。左侧选择“文件”页。点击“添加”在“逻辑名称”里填写一个有意义的名字比如“示例库_Data02”。“文件类型”保持“数据”“文件组”一般选PRIMARY。如果需要独立文件组先创建文件组再到这里选择。“初始大小”建议按实际预估填写单位是MB。不要填1MB然后指望自动增长频繁增长会产生文件碎片。“自动增长”区域可以设置按MB增长和最大文件大小建议按固定MB比如512MB或1024MB增长不要选“按百分比”尤其是大库按百分比会导致单次增长量越来越大很难控制。“路径”填写新文件的存放目录确保目录存在且SQL Server服务账号有权限。点击“确定”系统会立即创建ndf文件并添加到数据库中。这里有个容易忽略的点SSMS里设置的“路径”是指物理目录最终生成的文件扩展名会是.ndf逻辑名称NAME和物理文件名FILENAME是两个不同的东西逻辑名是SQL Server内部使用的标识物理名是磁盘上的文件名。后续做移动文件、修改文件属性时用到的是逻辑名不是物理名。2.3 脚本方式一句话加文件方便批量管理图形界面适合单机操作如果有多台服务器要批量处理我建议直接用T-SQL脚本可控性更强。给数据库添加数据文件的核心语句是USE [master]; GO ALTER DATABASE [示例库] ADD FILE ( NAME N示例库_Data02, FILENAME ND:\SQLData\示例库_Data02.ndf, SIZE 1GB, MAXSIZE UNLIMITED, FILEGROWTH 512MB ); GO参数说明NAME逻辑文件名数据库内必须唯一建议和库名关联。FILENAME物理文件路径目录必须已经存在。SIZE初始大小单位可以是KB、MB、GB。MAXSIZE最大大小可以不设默认为“无限制”但建议根据磁盘空间评估后设置。FILEGROWTH自动增长步长区分MB和百分比建议固定MB。如果你要添加到非主文件组需要在语句末尾指定ALTER DATABASE [示例库] ADD FILE ( NAME N示例库_Data02, FILENAME ND:\SQLData\示例库_Data02.ndf, SIZE 1GB, FILEGROWTH 512MB ) TO FILEGROUP [FG_2024];在执行前确认文件目录有权限不然会报操作系统错误5拒绝访问。我在生产环境里见过的失误大多出在这里不是语法错误而是D盘目录没有给SQL Server服务账号权限结果文件创建失败还留下一个半成品文件。2.4 文件组规划别把所有东西都压在一个篮子里文件组是SQL Server用于组织数据文件的逻辑单元。一个数据库至少有一个主文件组PRIMARY主文件组可以包含mdf和若干个ndf。数据库创建时可以额外定义用户文件组。把ndf加到专用文件组主要有两个好处一是可以独立管理文件例如将某个文件组设为只读二是结合分区表和分区方案可以按时间或按业务把数据分散到不同磁盘。要新建文件组脚本是ALTER DATABASE [示例库] ADD FILEGROUP [FG_2024];然后把文件加到该文件组最后把需要放进去的表移动过去。注意已有表不能简单“移动”到新文件组对于有聚集索引的表可以通过重建聚集索引的方式迁移CREATE UNIQUE CLUSTERED INDEX [PK_订单表] ON [dbo].[订单表] ([订单ID]) WITH (DROP_EXISTING ON, ONLINE ON);实际操作中要先把聚集索引创建在新文件组再DROP_EXISTING表数据会跟着一起迁移。如果没有聚集索引堆表移动起来更麻烦需要先把表改为聚集索引表或者用SELECT INTO新表再删旧表。日常维护中这种结构调整通常安排在低峰期并且会提前备份。3. 分离和附加数据库图形界面与T-SQL的完整实操3.1 分离数据库是什么什么时候用分离Detach是把数据库从SQL Server实例上卸载但保留物理文件mdf、ldf、ndf不动的操作。分离之后实例上不再有该数据库用户连接全部断开但文件还在磁盘上可以复制、移动之后随时可以重新附加。适合用分离的场景主要是迁移数据库到另一台服务器、把数据文件移动到其他磁盘、备份后带走文件。不适合的场景也要说清楚如果只是为了备份应该用BACKUP DATABASE而不是分离如果数据库参与了AlwaysOn可用性组、数据库镜像或复制不能直接分离否则会破坏副本状态。需要特别提醒的是分离前最好确保数据库没有活动连接并且已经执行了检查点和清理工作。如果有连接未断开分离会失败SSMS会提示数据库正在使用中。3.2 SSMS分离操作步骤与参数含义SSMS里分离的入口右键数据库 - 任务 - 分离。进入分离窗口后你会看到“数据库”列表下面有几个复选框“删除连接”勾选后系统会尝试终止所有与该数据库相关的连接。如果库是生产库有正在跑的事务勾选它会中断事务可能导致未完成的操作回滚所以建议在维护窗口操作。“更新统计信息”默认勾选。分离过程中更新统计信息可以在附加后减少统计信息过时带来的性能问题。更新统计信息会多花点时间但对大多数库值得保留。“保留全文目录”如果数据库使用了全文索引勾选后保留全文目录不勾选会删除目录附加后需要重建。大多数情况下保留。点击“确定”后实例会执行分离最终在“对象资源管理器”里数据库消失但磁盘上文件仍在。这时你就可以去文件目录里复制mdf、ldf和ndf文件了。如果想用脚本分离命令是USE [master]; GO EXEC sp_detach_db dbname N示例库, skipchecks Nfalse; GOsp_detach_db的参数不多skipchecks控制是否跳过统计信息更新。如果库比较大可以考虑用keepfulltextindexfile Ntrue保留全文索引文件。注意分离前如果库处于可疑状态可能分离失败需要使用其他手段这里不做展开。3.3 SSMS附加数据库操作步骤与脚本写法附加是把分离后的数据库文件重新挂到实例上。SSMS里右键“数据库”节点 - 附加。在弹出的“附加数据库”窗口里点击“添加”找到mdf文件。选中mdf后窗口下方会显示数据库的详细信息包括原始文件路径和当前文件路径。如果ldf和ndf文件没有丢失SQL Server会自动识别并把“当前文件路径”填好。如果ldf不在原位置你可以手改路径如果ldf已经丢失选中那一行点“删除”然后附加时就只带数据文件SQL Server会尝试重建日志。脚本方式有两种。保留原有日志文件的附加USE [master]; GO CREATE DATABASE [示例库] ON ( FILENAME ND:\SQLData\示例库.mdf ), ( FILENAME ND:\SQLData\示例库_log.ldf ) FOR ATTACH; GO无日志文件、由SQL Server重建日志的附加USE [master]; GO CREATE DATABASE [示例库] ON ( FILENAME ND:\SQLData\示例库.mdf ) FOR ATTACH_REBUILD_LOG; GOATTACH_REBUILD_LOG会创建一个新日志文件但前提是数据库能正常关闭检查点并且数据文件没有损坏。如果mdf之前是异常断电留下的重建日志可能失败。所以分离再附加和直接拷文件再附加还是有不少区别的。3.4 附加后的善后孤立用户、权限和连接串附加完成后最常见的后续问题就是“能连接数据库但登录不了”。原因是数据库里的用户database user和服务器登录名server login的SID不匹配。原来的服务器登录名没有跟着数据库走附加到新实例后新实例里没有同名的登录名或者有同名登录名但SID不一致就会报错。解决方法是把孤立用户映射到登录名。SQL Server 2008及以上版本用ALTER USERUSE [示例库]; GO ALTER USER [用户名] WITH LOGIN [服务器登录名]; GO如果是旧版本可以用存储过程EXEC sp_change_users_login update_one, 用户名, 登录名;另外附加后业务连接串里如果写的是原实例名也要改。如果附加时给库改了名连接串和存储过程中的库名引用也要一起改。这些小问题看着不大但往往在生产切换时容易漏一旦漏了应用启动报错排很久都找不到原因。4. 高频报错和排查技巧实录4.1 附加时提示“操作系统错误5拒绝访问”出现这个报错说明SQL Server服务进程没有文件目录的访问权限。解决办法是先确认运行的SQL Server服务账号是什么在“服务”里查看SQL Server服务的“登录为”然后给对应的文件目录授予该账号的读取和写入权限。实操中还有一个容易踩的坑你当前用SSMS登录时是Windows管理员附加操作能成功但一会儿服务重启后系统里以SQL Server服务账号运行的进程访问不到这个目录就会出现间歇性报错。所以权限设置不要只针对当前登录用户要针对SQL Server服务账号。也可以将mdf文件目录设置成Everyone读取不建议生产环境千万别这么干权限面太大最好精确到账号。4.2 附加时提示“版本错误”SQL Server的数据文件版本号是不能向后兼容的。比如SQL Server 2019的mdf文件不能附加到SQL Server 2014实例上因为低版本不认识高版本的文件格式。反过来低版本的库可以附加到高版本实例附加后文件版本会升级之后就不能再降回去。遇到跨版本附加的需求正确的做法不是复制文件而是用BACKUP DATABASE在高版本实例上做备份然后恢复到兼容的目标实例。如果目标是低版本无法直接还原高版本备份正确的迁移路径是在低版本实例上创建数据库再通过数据导入方式搬数据或者使用相同版本或可兼容版本的备份文件。这个逻辑要记清楚免得来回折腾。4.3 文件正在使用中无法复制或附加附加时提示“文件正在使用中”通常有两个来源一是文件被另一个数据库实例使用也就是说你试图附加一个已经被其他实例附加的库二是杀毒软件、云同步工具、备份软件等进程正在读取这个文件。解决办法是使用Process Explorer或系统自带资源监视器搜索文件句柄确认哪个进程占用了文件。如果文件在当前实例上被占用先分离或者停掉相关实例再复制文件。如果是杀毒软件把数据库文件目录加入排除列表很多DBA都会做这一步防止杀毒软件对数据库文件进行实时扫描造成锁文件和性能问题。这个问题的排查思路和我处理过的一个案例很像某台服务器的SQL Server一直无法附加一个库最后发现是另一台机器上的SQL Server实例通过共享路径打开着同一个文件导致文件被锁。数据库文件最好放在实例本地磁盘不要放在网络共享目录上尤其是在生产环境。4.4 分离时提示“数据库正在使用中”连着的一堆连接怎么清分离时遇到这个提示最大的可能性是还有用户连接占着数据库。SSMS分离窗口里勾选“删除连接”可以自动断开这些连接但这会中断正在执行中的事务回滚操作可能要一段时间。如果库很大建议先手动确认连接来源。可以通过下面语句查询当前连接SELECT session_id, login_name, status, host_name, program_name FROM sys.dm_exec_sessions WHERE database_id DB_ID(N示例库);杀掉不用的连接KILL 57;之后再用脚本分离USE [master]; GO ALTER DATABASE [示例库] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO EXEC sp_detach_db dbname N示例库; GO注意ROLLBACK IMMEDIATE会回滚正在执行的事务生产库慎用。如果只是少数开发连接直接勾选“删除连接”就行。如果你只是想临时断开连接而不是分离记得用完SINGLE_USER后要改回MULTI_USER。4.5 附加后数据库显示“只读”或无法写入附加时如果数据库文件被标记为只读文件系统的只读属性SQL Server会以只读方式附加应用层写入时报错。检查方法是右键mdf文件查看“属性 - 常规 - 只读”如果勾选就去掉。特别容易出现在U盘、网盘同步目录、压缩包解压后的文件上。附加前最好把文件从压缩包里完整解压出来并且不要直接从只读介质上附加。另一个容易忽略的权限问题是如果服务账号对目录只有读取权限没有写入权限数据库虽然能附加但无法正常写入日志或数据表现就是应用写入时报“数据库或文件处于只读状态”。把目录的安全权限配成“读取和执行、列出文件夹内容、写入”问题就解决了。4.6 日志文件过大或丢失的应对日志文件过大可以通过备份日志后再收缩处理USE [示例库]; GO BACKUP LOG [示例库] TO DISK ND:\backup\示例库_log.bak; GO DBCC SHRINKFILE (N示例库_log, 1024); GO注意必须先备份日志否则日志无法截断收缩可能没有效果。数据库恢复模式如果是“完整”长时间没有日志备份是日志膨胀的主要原因。恢复模式是“简单”的库日志会自动截断但也不能完全避免物理文件过大偶尔做DBCC SHRINKFILE是有必要的。如果ldf文件丢了附加数据文件时用FOR ATTACH_REBUILD_LOGCREATE DATABASE [示例库] ON (FILENAME ND:\SQLData\示例库.mdf) FOR ATTACH_REBUILD_LOG; GO这个命令会生成一个新的日志文件但有一个风险如果数据库没有正常关闭比如异常断电重建日志可能失败。所以在日常运维里我一般建议不要把ldf和mdf分开存放太远至少都在同一台机器的不同分区上别把mdf放在本地、ldf放在网络盘时间久了出问题很难排查。5. 最后再分享几个实战小技巧查看数据库文件和文件组信息最常用的是sys.master_filesSELECT name, type_desc, physical_name, size, growth, is_percent_growth, state_desc FROM sys.master_files WHERE database_id DB_ID(N示例库);size字段的单位是8KB页面数乘以8就得到KB。这个查询在做迁移前评估、排查文件路径错误时非常有用我建议你把它存成常用脚本。如果需要移动数据文件比如从D盘迁到E盘很多人直接复制文件然后改路径结果报错。正确流程是先在SQL Server里修改逻辑路径ALTER DATABASE [示例库] MODIFY FILE (NAME N示例库, FILENAME NE:\SQLData\示例库.mdf);再分离数据库手动把文件移到新目录最后附加。注意MODIFY FILE只是修改元数据中的路径不会真的移动文件物理文件必须自己搬顺序反了会报路径错。我个人在实际操作中最深的一个体会是很多人把“分离/附加”和“备份/还原”混为一谈这不是一回事。备份/还原是SQL Server支持的标准迁移和恢复方案会生成备份文件安全性高分离/附加是把数据库文件直接卸载再挂载速度快但风险也高中间任何一个环节出错都可能影响到源数据库。能用备份还原解决的场景我一般不会用分离附加只有在需要移动整个数据文件物理位置、或者从异常损坏的实例上抢救数据时分离附加才更有价值。另外每次做分离附加或文件添加操作之前我都会先记下当前sys.master_files的输出结果和数据库恢复模式。这样即使操作中出问题至少手里有一份基线数据可以对照排查。这些小习惯比临时翻文档要有用得多。