简介跟随郝斌老师学习SQL Server 2005的完整笔记适合数据库初学者或希望系统梳理知识点的读者。笔记围绕数据库如何解决数据存储问题展开从三个方面进行阐述在数据存储部分详细讲解字段、记录、表的结构以及主键、外键、唯一键、非空、CHECK、DEFAULT、触发器等多种约束的作用并给出建表SQL示例在数据操作部分覆盖INSERT、UPDATE、DELETE、T-SQL、存储过程、函数和触发器的典型应用在数据展示部分突出SELECT查询的核心用法。此外笔记还记录了如何创建数据库、附加和分离数据库、设置登录用户名和密码、删除数据库等必备操作同时通过实际SQL命令演示了约束的写法。资源仅包含1个docx文档压缩包大小387KB内容按照知识点顺序编排附有大量注释和示例语句便于学习者对照实践。目前已有132人学习下载对于巩固SQL Server 2005基础、备考或日常查询复习都很有帮助。1. 跟随郝斌老师学习 SqlServer2005这份笔记到底在记什么如果你是从 SqlServer 2005 开始接触数据库的一线工程师大概率对郝斌老师的视频教程不陌生。那份《跟随郝斌老师学习SqlServer2005总结的笔记.docx》并不是简单的上课抄录而是一份把安装部署、T-SQL 语法、备份恢复、性能调优串成一条线的实战手册。它的价值在于不是教你怎么点鼠标建表而是告诉你一个数据库实例从装好到跑稳中间要过哪些坎。这篇博文就沿着这份笔记的技术主线把 SqlServer2005 里最值得反复琢磨的部分拆开讲。内容包括核心机制、最小安装配置、备份恢复操作、常见翻车场景和进阶验证方向。适合两类人一类是刚入行、跟着视频学但缺一条落地路径的初级 DBA另一类是接手老系统、需要快速补上 SqlServer2005 运维细节的开发运维工程师。读完你能照着操作也知道每步命令背后的逻辑不至于出了问题再翻文档。2. 为什么 2005 值得学先理解这几处关键设计2.1 SqlServer2005 在数据库家族里的位置SqlServer2005 是微软数据库产品线里一个分水岭版本。它之前的 SqlServer2000 还是以企业版、标准版区分功能很多高级特性只有企业版才有到了 2005微软把功能分层做得更细引入了 SQL Server Management Studio 这样一个统一管理入口也首次把数据库引擎、分析服务、集成服务、报表服务拆成相对独立的组件。这份笔记里反复强调的“实例”概念就是从这个版本开始真正被用户感知的。一个实例对应一组独立的服务进程和端口默认实例占用 1433 端口命名实例则使用动态端口。你在一台机器上装多个实例它们之间的 master、tempdb、msdb 是隔离的但资源是共享的。郝斌老师的课程里对这块讲得很细因为这直接关系到你怎么规划一台服务器。如果你只知道点“下一步”装完数据库不理解实例和数据库的边界后面做权限隔离或者迁移的时候会非常吃力。从实际运维角度看SqlServer2005 的意义在于它确立了后续版本的很多行为习惯。比如动态管理视图DMV开始出现虽然不如 2008 以后那么丰富但 sys.dm_exec_requests 这类视图已经可以用来排查阻塞和等待比如错误日志开始统一写入 LOG 目录下的 ERRORLOG 文件可以配置循环覆盖。你学会了 2005 的操作方式转到 2008、2012 甚至更高版本很多思维是通用的只是 UI 变了、性能更强了。2.2 逻辑管理结构数据库、文件组、文件的关系笔记里有很大篇幅在讲“数据库”“文件组”“文件”这三层关系这是理解 SqlServer2005 存储体系的基础。一个数据库至少包含一个主文件组PRIMARY主文件组里至少有一个主数据文件.mdf和一个日志文件.ldf。你可以额外创建用户文件组里面放多个次要数据文件.ndf从而把数据分散到不同磁盘减轻单盘 IO 压力。文件组的意义在于表和索引可以指定建立在哪个文件组上。当你给一个大表建索引时如果索引建在另一个文件组那么索引的读写就和表的读写分离到不同物理磁盘查询性能会有明显提升。这在 SqlServer2005 时代是常见的性能优化手段因为那时候 RAID 和 SSD 还不像现在这么普及磁盘 IO 往往就是瓶颈。笔记里提到的一个典型案例是把历史归档表放到单独的慢速磁盘文件组把热数据留在主文件组以此控制存储成本。创建文件组和文件的脚本也不复杂但这里的关键是“顺序”先建文件组再建文件最后把文件加到文件组里。顺序错了比如直接指定文件组名但还没建就会报错。还有一个小细节是日志文件没有文件组的概念它独立存在而且一个数据库可以有多个日志文件但只会顺序写其中一个满了再写下一个并不是并行写所以加日志文件对性能提升没什么帮助只是解决空间不足的问题。2.3 SQL Server 服务的启动方式和恢复模式SqlServer2005 的服务有三种启动模式自动、手动、禁用。数据库引擎服务平时设为自动启动没问题但 SQL Server Agent 服务要看你有没有定时任务。如果你的环境只是做开发测试Agent 不启动完全不影响日常增删改查但一旦有作业在跑比如定时备份或索引维护Agent 必须运行否则作业只会挂起不执行。恢复模式是另一个贯穿整个笔记的关键词。完整恢复模式会把所有操作都记入日志支持精确到某个时间点的还原简单恢复模式会在每次检查点之后自动截断日志日志文件不容易膨胀但只能还原到最后一次完整备份或差异备份的时间点不能做时间点恢复。大容量日志恢复模式则是为了批量导入数据时减少日志量而设计的。我在实际项目里最常见的情况是生产库用了完整恢复模式但没人定期备份日志结果日志文件涨到几十个 GB把磁盘塞满。这不是 2005 独有的问题而是因为恢复模式选择之后没有配套“定期备份日志”的策略。笔记里建议的做法是完整恢复模式必须有日志备份作业频率按业务可接受的数据丢失量来定比如每 15 分钟一次如果业务能接受一天的数据丢失直接切简单恢复模式更省心。2.4 事务日志的工作原理为什么删数据文件会爆炸SqlServer2005 里最容易让人误解的就是事务日志。它记录的不是数据本身而是数据页的变化描述。当你执行一条 UPDATE 语句时数据库先从数据页读取旧值到内存修改后生成日志记录最后才会把脏页写入磁盘。这个过程叫“先写日志后写数据”也就是 Write-Ahead Logging。如果数据库在写入数据页之前崩了重启后能通过日志把未完成的事务回滚保证一致性。笔记里有一个很形象的比喻日志文件是数据库的“后悔药”。只要日志记录还在事务就可以回滚数据库就可以恢复。所以日志文件不是随便能删的。有些人看到日志文件太大直接把它删掉这种行为会让数据库在下次启动时无法正常恢复往往只能重启服务甚至可能损坏整个库。如果你碰到日志文件巨大正确做法是先把备份方式改成简单恢复模式再执行一次 CHECKPOINT然后执行 DBCC SHRINKFILE 收缩日志文件。千万记住收缩后要把恢复模式改回来否则日志备份链断掉后续恢复计划会失效。3. 把 SqlServer2005 落到本地环境安装与最小可用配置3.1 安装 SqlServer2005 的版本选择与前置检查如果你现在想搭一套 SqlServer2005 环境来验证笔记里的操作最大的障碍不是安装包本身而是操作系统兼容性。SqlServer2005 官方支持 Windows Server 2003、Windows XP、Windows Vista 这一代系统。到了 Windows 7 和 Windows 10 上虽然能装但需要处理不少兼容性问题。我的建议是直接用虚拟机装 Windows Server 2003 或 Windows XP然后安装 SqlServer2005 开发版或企业版这样最贴近当年的教学环境踩坑也少。安装之前要检查几件事系统不是域控制器、IIS 没有启用冲突的端口、防火墙允许 1433 端口通过、本机有足够内存512MB 以上即可跑起来1GB 会更流畅。SqlServer2005 安装包分为服务端和客户端如果你只需要连接别的数据库服务器装个客户端工具就行如果要建库建表做实验就必须装数据库引擎。郝斌老师的课程里用的是开发版因为它和企业版功能几乎一致但授权费用更低很适合学习。还有一点很容易忽略安装时选择“混合模式”还是“Windows 身份验证模式”。混合模式会启用 sa 账号允许 SQL 账号连接Windows 身份验证模式只允许系统账号登录。学习阶段我建议选混合模式并且设置一个强密码因为后面很多连接工具比如 JDBC、ODBC在 Windows 验证模式下配置麻烦混合模式可以直接用 sa 登录。等你理解了认证机制再切到 Windows 验证也不迟。3.2 安装完成后必做的五个配置项安装完 SqlServer2005 之后不要急着建表先把下面这五个配置项调好这是笔记里“环境准备”一章的核心内容。第一确认 SQL Server 服务和 SQL Server Agent 的启动模式第二调整数据库默认数据目录和日志目录不要放在 C 盘系统盘第三配置连接属性里的“允许远程连接”第四设置 sa 账号的密码策略第五把错误日志的保存天数调大方便日后排查问题。这些配置项在 SQL Server Management Studio 里通过图形界面就能完成但我更推荐直接用系统存储过程来做因为脚本可复用、可审计。比如设置 sa 密码和启用混合模式可以用 sp_password 和 sp_configure 配合完成。这里给一段参考脚本注意在查询分析器里执行前先确认你是在 master 数据库上下文中。-- 启用 SQL Server 和 Windows 身份验证模式需要先关掉远程连接限制 EXEC sp_configure show advanced options, 1; RECONFIGURE; EXEC sp_configure remote access, 1; RECONFIGURE; -- 修改 sa 密码并启用 sa 登录注意密码要符合系统策略 ALTER LOGIN sa WITH PASSWORD StrongPass_2024, CHECK_POLICY ON; -- 将默认数据目录指向 D 盘避免系统盘空间不足 USE master; EXEC xp_instance_regwrite NHKEY_LOCAL_MACHINE, NSoftware\Microsoft\MSSQLServer\MSSQLServer, NDefaultData, REG_SZ, ND:\MSSQL\Data;这段脚本的逻辑是第一段先把 show advanced options 打开让 sp_configure 能看到更多配置项第二段开启远程访问否则局域网内其他机器连不上第三段修改 sa 密码CHECK_POLICY 开启后强制密码复杂度第四段通过注册表把默认数据文件位置改掉这样后续 CREATE DATABASE 时如果不指定路径就会自动放到 D 盘。参数说明里最值得关注的是 CHECK_POLICY它对应 Windows 的密码复杂度策略。如果你用的是老系统或测试环境开了之后可能遇到“密码太简单不让改”的问题此时可以临时把 CHECK_POLICY 设为 OFF改完再打开。注意 xp_instance_regwrite 这个扩展存储过程在 SqlServer2005 里可用到了更高版本虽然还在但不推荐当成常规手段更稳妥的做法是在 SSMS 图形界面里改数据库默认位置。3.3 最小连接验证从本地到远程配置完成后最重要的就是验证能不能连上。本地用 SQLCMD 或 SSMS 连接是第一步远程连接是第二步。SqlServer2005 自带 sqlcmd 命令行工具连接测试非常适合写进自动化脚本。下面这段命令展示的是用 SQLCMD 以 sa 身份登录本地默认实例然后执行一条简单的查询并退出。sqlcmd -S localhost -U sa -P StrongPass_2024 -Q SELECT VERSION -l 5这条命令里的参数含义分别是-S 指定服务器地址和实例名localhost 表示本机默认实例-U 和 -P 是登录账号和密码-Q 表示执行后退出适合脚本调用-l 5 表示连接超时设为 5 秒。如果你看到返回结果里有“Microsoft SQL Server 2005”字样说明本地连接成功。如果本地连不上先看 SQL Server 服务有没有启动再看防火墙是否拦了 1433 端口。远程连接测试还要多一步确认服务器端的 SQL Browser 服务是否启动以及网络里有没有路由器或安全组没放行端口。我遇到过不少情况是本地能连、远程连不上最后查出是 Windows 防火墙把 sqlservr.exe 给拦了而不是端口问题。你可以临时关掉防火墙测试或者直接在防火墙里加一条允许 1433 端口的入站规则验证完再收紧。远程验证还有一种更简单的办法在另一台机器的 SSMS 里输入“服务器IP,端口号”格工例如 192.168.1.10,1433。注意逗号必须是英文逗号很多人第一次写成了中文逗号连接报错还以为是网络问题。如果你用的是命名实例写法是“服务器IP\实例名”但命名实例的端口是动态的可能还需要 SQL Browser 服务配合才能解析。4. 备份与恢复把数据安全做到能睡安稳觉4.1 三种备份类型的功能边界SqlServer2005 的备份体系是理解整个运维工作的核心。完整备份Full Backup备份整个数据库的数据文件和部分日志生成一个备份文件是恢复工作的基础差异备份Differential Backup备份自上次完整备份以来所有变化的数据区文件比完整备份小很多恢复时先恢复完整备份再恢复最新的差异备份可以减少恢复时间日志备份Transaction Log Backup备份自上次日志备份以来所有事务日志用于时间点恢复是完整恢复模式下高频操作的首选。三种备份的配合关系可以用一句话概括完整备份决定你能恢复到哪个时间基点日志备份决定你能恢复到那个基点之后的哪一秒。差异备份夹在中间作用是减少恢复时需要重放的日志数量。举个例子你每周日做完整备份每天凌晨做差异备份每半小时做一次日志备份。如果周三上午 10 点数据库坏了你需要先恢复上周日的完整备份再恢复周三凌晨的差异备份然后重放 10 点之前半小时内的日志备份最后能恢复到 10 点前最后一笔日志记录的位置。笔记里特别强调了一点不要用完整备份替代差异备份和日志备份。如果只做完整备份恢复时最多只能恢复到备份完成的那一刻备份之后的新增数据全部丢失。很多初学者以为“每天做一次完整备份就够了”真出事时发现丢了一整天数据才开始理解三种备份的分工。备份不是把数据复制一份就行而是要按“恢复时间点”的需求反推策略。4.2 用 T-SQL 脚本实现自动化备份图形界面的备份向导适合临时用一次生产环境必须用脚本自动化。SqlServer2005 里最常用的备份方式是 BACKUP DATABASE 和 BACKUP LOG 语句。下面这段脚本展示的是创建一个带时间戳的完整备份文件并把备份记录写入 msdb 的备份历史表。这段脚本可以放进 SQL Server Agent 作业里每周日执行一次。-- 定义备份文件路径使用时间戳避免覆盖 DECLARE backupFile NVARCHAR(255); SET backupFile ND:\Backup\MyDB_ CONVERT(VARCHAR(8), GETDATE(), 112) N_ REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), :, ) N.bak; -- 执行完整备份带 INIT 和 FORMAT 参数确保文件干净 BACKUP DATABASE [MyDB] TO DISK backupFile WITH INIT, FORMAT, NAME NMyDB 完整备份, COMPRESSION OFF; -- 查看备份结果 SELECT database_name, backup_start_date, backup_finish_date FROM msdb.dbo.backupset WHERE database_name NMyDB ORDER BY backup_finish_date DESC;这段脚本有几个关键参数。INIT 表示如果目标文件已存在则覆盖它如果不用 INIT默认会追加到现有备份文件文件会越来越大恢复时还容易搞混备份集。FORMAT 参数会重新初始化备份介质写入新的介质标头建议做完整备份时带上防止备份文件被意外覆盖时留下残留数据。COMPRESSION OFF 是因为 SqlServer2005 原生不支持压缩备份2008 企业版开始才有这里显式写出来是为了提醒你别在 2005 上写 COMPRESSION ON否则会直接报错。时间戳格式里用了 CONVERT 和三段式日期格式 112得到的是类似“20241115”的字符串再用 108 得到“143022”格式的时间并去掉冒号。合起来 MyDB_20241115_143022.bak 这样的文件名一眼就能看出是哪天哪次做的备份恢复时选文件也直观。如果你觉得一段段拼字符串太啰嗦也可以直接用 REPLACE 加 CONVERT 组合或者用更简单的格式比如只保留日期不保留时间让作业每天覆盖一次但那样历史备份就只剩一份恢复时没有回退余地我不推荐。日志备份的脚本和完整备份几乎一样只是把 BACKUP DATABASE 换成 BACKUP LOG。日志备份的 INIT 参数要特别小心日志备份文件如果带 INIT 覆盖会把之前所有日志备份覆盖掉恢复链就断了。所以日志备份的脚本里我一般不加 INIT让每个日志备份追加到同一个文件里或者每天生成一个新文件这样更安全。4.3 还原操作的顺序完整还原到差异还原再到日志还原还原数据库不是简单地把备份文件导回去顺序和状态控制非常关键。SqlServer2005 的还原命令支持 NORECOVERY 和 RECOVERY 两个状态选项。NORECOVERY 表示还原后数据库保持“正在还原”状态不能被访问但可以继续应用后续备份RECOVERY 表示还原完成数据库可以正常使用。你要还原多个备份时除了最后一个其他都必须用 NORECOVERY否则后面的差异备份和日志备份就无法应用。下面这段脚本演示了如何把完整备份、差异备份和日志备份依次还原到某个时间点。假设完整备份文件是 MyDB_Full.bak差异备份是 MyDB_Diff.bak日志备份是 MyDB_Log.bak。-- 还原完整备份数据库进入 Restoring 状态 RESTORE DATABASE [MyDB] FROM DISK ND:\Backup\MyDB_Full.bak WITH NORECOVERY; -- 还原差异备份数据库继续处于 Restoring 状态 RESTORE DATABASE [MyDB] FROM DISK ND:\Backup\MyDB_Diff.bak WITH NORECOVERY; -- 还原日志备份并恢复到指定时间点 RESTORE LOG [MyDB] FROM DISK ND:\Backup\MyDB_Log.bak WITH RECOVERY, STOPAT N2024-11-15T10:30:00;这段脚本里的 STOPAT 是日志恢复最值钱的参数。它让你能把数据库恢复到某个精确时刻比如误删数据前的一分钟。使用 STOPAT 要求日志备份文件里包含的目标时间点之前的事务日志记录完整。如果日志备份里已经有 10:30 之后的记录STOPAT 会止在 10:30之后的日志不会应用。这里有个坑STOPAT 的时间格式必须是国际标准时间格式 YYYY-MM-DDTHH:MM:SS中间用 T 隔开不能写空格否则会报错。还有一点要注意RESTORE 操作的执行者必须有相应权限。SqlServer2005 里默认只有 sysadmin 和 dbcreator 角色能执行还原。如果你用的账号只有 db_owner 权限还原时会提示权限不足。我曾经在给客户做恢复演练时吃过这个亏当时用的是一个应用程序账号登录名权限只有普通用户级别执行 RESTORE 直接报“没有权限执行该操作”后来用 sa 才跑通。所以做恢复操作前先确认账号有 sysadmin 角色省得来回折腾。4.4 验证备份文件可用性恢复后的一致性检查备份做完了不代表万事大吉你必须验证备份文件真的可以还原成可用数据库。最稳妥的办法是在另一台机器或同一个实例上恢复成一个临时库名然后跑 DBCC CHECKDB 检查一致性。下面这段脚本先把完整备份还原成一个临时库 TempRestore再执行一致性检查最后把临时库删掉整个过程不影响生产库。-- 将备份还原为临时库验证备份文件可用性 RESTORE DATABASE [TempRestore] FROM DISK ND:\Backup\MyDB_Full.bak WITH MOVE NMyDB_Data TO ND:\MSSQL\Data\TempRestore.mdf, MOVE NMyDB_Log TO ND:\MSSQL\Data\TempRestore_log.ldf, RECOVERY; -- 对临时库执行一致性检查 DBCC CHECKDB(NTempRestore) WITH NO_INFOMSGS; -- 验证通过后删除临时库 DROP DATABASE [TempRestore];这段脚本里的 MOVE 参数很关键。不同数据库的主数据文件和日志文件的逻辑名不同你把 A 库的备份还原成 B 库时物理文件名不能和原来一样否则会覆盖原文件。MOVE 用来指定新的物理路径和文件名。如果你不确定逻辑名可以先执行 RESTORE FILELISTONLY FROM DISK 备份文件路径 查看再决定 MOVE 参数怎么写。第一次做这个操作的人十有八九会在这里报错因为忘了 MOVE直接写 WITH RECOVERY 导致提示文件已存在或文件被占用。DBCC CHECKDB 是 SqlServer2005 里最可靠的一致性检查工具。它会检查数据库页、索引结构、链接关系等任何物理损坏或逻辑损坏都能报告出来。加上 NO_INFOMSGS 参数后只显示错误信息不会刷屏输出正常状态。如果你的数据库非常大CHECKDB 运行时间会很长建议在维护窗口执行或者只对关键表做 CHECKTABLE。不过对备份文件做一次性检查还是值得的总比真出事时才发现备份是坏的强。5. SqlServer2005 避坑笔记六个让人翻车的真实场景5.1 sa 登录失败密码对却连不上现象用 sa 登录 SqlServer2005输入正确密码后报错“用户 sa 登录失败”。解决思路三步走先确认服务端是不是启用混合验证模式再确认 sa 账号没有被锁定或禁用最后检查密码里有没有被系统策略挡掉。最常见的其实是安装时选了 Windows 验证模式导致 sa 即使密码正确也登不上。处理办法是用 Windows 账号登录 SSMS然后执行下面这段脚本修改认证模式并启用 sa。USE master; GO EXEC sp_configure show advanced options, 1; RECONFIGURE; GO EXEC sp_configure mixed security, 1; RECONFIGURE; GO ALTER LOGIN sa ENABLE; GO ALTER LOGIN sa WITH PASSWORD N新密码; GO这段脚本做的事是先把高级选项打开再设置混合安全模式接着启用 sa 登录最后重置 sa 密码。注意‘mixed security’这个配置项在 2005 里对应的是“SQL Server 和 Windows 身份验证模式”不同版本叫法略有不同但存储过程名称不变。执行完这些操作后重启一次 SQL Server 服务让配置完全生效。另外一个容易被忽略的原因是sa 账号被多次错误尝试后如果系统配置了账户锁定策略sa 会被自动锁定。SqlServer2005 默认不开启密码锁定策略但如果你在安装时选择了强制密码策略并且在组策略里设置了阈值大量错误登录会让 sa 被锁。解锁方法同样是先用 Windows 账号登录执行 ALTER LOGIN sa WITH CHECK_POLICY OFF然后再改密码。5.2 自动增长和收缩导致的性能抖动现象数据库表数据量不大但文件占用空间很大而且磁盘 IO 很高查询偶尔很慢。原因多半是自动增长设置不合理。默认情况下SqlServer2005 的自动增长是按百分比增长的数据文件默认增长 10%日志文件默认增长 10%。如果数据量本身就大且增长快按百分比增长会频繁触发文件扩展扩展时要分配新空间并做零填充这个操作会锁住相关页面导致阻塞。解决办法是把数据文件和日志文件的自动增长改为固定大小增长比如每次 512MB同时设置合理的最大文件大小避免文件无限增长把磁盘写完。修改文件属性的脚本如下。ALTER DATABASE [MyDB] MODIFY FILE (NAME MyDB_Data, FILEGROWTH 512MB, MAXSIZE 20GB); GO ALTER DATABASE [MyDB] MODIFY FILE (NAME MyDB_Log, FILEGROWTH 256MB, MAXSIZE 2GB); GO这段脚本把数据文件改成每次增长 512MB、上限 20GB日志文件每次增长 256MB、上限 2GB。这样的好处是文件扩展次数大幅减少每次扩展分配的空间足够应对一段时间的写入性能抖动明显变轻。MAXSIZE 设上限是预防日志文件无限膨胀一旦接近上限你会收到错误日志告警而不是磁盘直接爆掉。要注意的是MODIFY FILE 不会立即改变现有文件大小只是修改属性。如果你想立刻看到文件变小还得配合 DBCC SHRINKFILE。收缩文件这个操作在很多文档里都建议不要在生产环境频繁做因为它会重建碎片可能影响查询性能。我一般只在日志文件异常增长后做一次即时收缩数据文件则从不主动收缩让它保持稳定大小。5.3 日志备份链断裂导致的恢复失败现象完整备份成功日志备份也成功但恢复时提示“无法应用此日志备份因为日志链断裂”。这个问题通常是有人在备份之间手动备份日志或者切换了恢复模式。比如你先做了完整备份然后有人把数据库从完整恢复模式改成简单恢复模式再改回来这会导致日志序列号不连续后续日志备份无法与完整备份衔接。处理办法是在做任何恢复模式切换后立刻做一次新的完整备份重置日志链。这个习惯要刻进骨子里。判断日志链是否完整可以查询 msdb.dbo.backupset 看每个备份的 first_lsn 和 last_lsn 是否有缺口。下面这段脚本可以检查 MyDB 的备份链完整性。SELECT type, first_lsn, last_lsn, backup_finish_date FROM msdb.dbo.backupset WHERE database_name NMyDB ORDER BY backup_finish_date;执行这脚本后你会看到完整备份和日志备份的 LSN 范围。如果日志备份之间有空档说明链断了。比如第一条日志备份的 first_lsn 是 100last_lsn 是 200第二条日志备份的 first_lsn 是 210中间少了 10~200 这一段那 200 到 210 之间的日志一定是丢了。绝大多数情况下这种断链是切换恢复模式或手工删除日志文件导致的。一旦链断了你只有两个选择放弃时间点恢复直接还原最新的完整备份或者重置备份链后做新的完整备份恢复到那个时间点之后。5.4 恢复模式切换后日志文件暴涨现象把数据库从完整恢复模式改成简单恢复模式日志文件不但没变小反而更大了。原因是切换恢复模式后日志文件里的虚拟日志文件在空间上做了重新划分但物理文件并不会自动收缩。还要注意简单恢复模式下虽然日志会被截断但截断的是逻辑上已完成的日志记录文件占用的磁盘空间还是在只有新建的日志记录才会尝试重用内部空间。所以碰到日志文件暴涨正确做法是先把恢复模式切到简单模式执行 CHECKPOINT 使活动日志截断然后用 DBCC SHRINKFILE 把日志文件收缩到合理大小。收缩后如果想保持时间点恢复能力再把恢复模式切回完整并立刻做一次完整备份作为新基线。下面这段脚本演示完整操作顺序。USE [MyDB]; GO ALTER DATABASE [MyDB] SET RECOVERY SIMPLE; GO CHECKPOINT; GO DBCC SHRINKFILE (MyDB_Log, 100); GO ALTER DATABASE [MyDB] SET RECOVERY FULL; GO BACKUP DATABASE [MyDB] TO DISK ND:\Backup\MyDB_AfterShrink.bak WITH INIT; GODBCC SHRINKFILE 的第二个参数 100 表示把文件收缩到 100MB。如果当前文件里有活动日志超出了这个目标值它会尽量收缩到能收缩的最小值。收缩完成后必须做一次完整备份因为恢复模式从简单切回完整后原来的日志备份链已经失效不建立新基线的话日志备份无法与历史完整备份衔接。有一个隐藏坑执行 CHECKPOINT 时如果当前库有长事务正在跑活动日志部分可能很大收缩不会达到预期效果。建议在业务低峰期操作或者先杀掉长时间运行的事务再收缩。找出长事务的方式可以查 sys.dm_tran_active_transactions 或 DBCC OPENTRAN看到持续超过一小时的事务就要小心了。5.5 排序规则不一致导致的乱码和连接中断现象应用连数据库后查询中文数据显示乱码或者两个库做跨库 JOIN 时报“无法解决排序规则冲突”。原因通常是安装 SqlServer2005 时选的排序规则不是中文简体比如选了默认的 SQL_Latin1_General_CP1_CI_AS导致中文字符存储和排序行为异常。解决方式是在建库时显式指定 Chinese_PRC_CI_AS 排序规则。如果已经建好的库排序规则不对可以用 ALTER DATABASE 修改排序规则但注意这个操作会重排所有字符数据数据量大时耗时很长而且过程中数据库会处于单用户模式不能访问。下面展示修改排序规则的命令和跨库 JOIN 时的临时解决方法。ALTER DATABASE [MyDB] COLLATE Chinese_PRC_CI_AS; GO -- 跨库查询时指定 COLLATE避免排序规则冲突 SELECT a.col1, b.col2 FROM DbA.dbo.TableA a INNER JOIN DbB.dbo.TableB b ON a.key1 b.key1 COLLATE Chinese_PRC_CI_AS;ON 条件里加 COLLATE 是最快的解决方式不需要修改任何数据库属性。但这也只是权宜之计查询性能会受一些影响。根治方法是统一所有库的排序规则。SqlServer2005 的排序规则在安装时一旦确定改起来非常痛苦新建的库不指定的话会继承实例默认排序规则所以最好在一开始就全局统一为 Chinese_PRC_CI_AS。判断当前排序规则可以在查询分析器里运行 SELECT SERVERPROPERTY(Collation)。如果整库的排序规则已经错了而且测试库可以接受重建最快的方式是导出数据到新库新库建库时指定正确排序规则然后导入数据。用 ALTER DATABASE 修改排序规则在 2005 上虽然可行但对复杂应用影响较大特别是被视图、函数、存储过程引用的列可能存在依赖关系导致修改失败。我有一次在生产库上修改排序规则直接影响了大量存储过程的执行计划排查了一天才定位到是 COLLATE 变化引起的。5.6 企业管理器对象资源管理器连接失败现象SSMS 打开后点击“连接”按钮提示“无法连接到服务器错误 26 或 40”。错误 26 大多数情况下意味着网络或端口不通错误 40 意味着无法打开与服务器的连接。原因分几类服务没启动、端口被防火墙拦、SQL Browser 服务没开导致命名实例解析失败、客户端和服务端的网络协议不一致比如服务端只开了 Named Pipes客户端用的是 TCP/IP。排查顺序从简单到复杂第一步检查服务是否启动第二步在服务器本机用 TCP/IP 连一次本机实例如果可以说明服务端正常第三步检查防火墙放行 1433 端口和 sqlservr.exe 程序第四步查看“SQL Server 配置管理器”里 TCP/IP 协议是否启用注意是“已启用”不是“已安装”。通常你安装 SqlServer2005 时如果选了默认实例TCP/IP 默认是启用的但如果中间有人改过协议配置可能被禁用。还有一种很容易见鬼的情况sqlservr.exe 被防火墙拦截了但你只放行了端口 1433。因为 SQL Server 除了监听 1433还会用动态端口接收某些连接特别是在有命名实例的时候。如果你确实只需要固定端口可以在配置管理器里把 TCP/IP 的动态端口设为空只保留 1433 作为静态端口然后把防火墙规则同时加端口和程序路径。这个做法我用了很多年能把这类问题挡掉一大半。6. 把这份笔记变成自己的验证练习与延伸方向学完笔记里的操作后光看不动手是记不住的。我的建议是搭一个最小环境按时间点恢复这个路径完整走一遍。具体做法是创建一个库建两张表插入几百条记录做一次完整备份再插入一批新数据做差异备份再插入几行做日志备份然后模拟误删除一批数据最后用 STOPAT 恢复到误删前的时间点。这一步如果跑通了你对备份恢复体系的信任度会完全不同。最后提醒一点SqlServer2005 早已停止主流支持生产环境如果还在用老版本建议尽早做升级评估。但掌握这套逻辑对你的价值不会淘汰——从 2005 到 2022实例、文件组、事务日志、恢复模式的底层思维几乎没变你学会的每个存储过程、每条 DMV 查询到新版本只会多不会少。真想进阶可以沿着 sys.dm_exec_requests、sys.dm_os_wait_stats 这些 2005 里已经开始出现的视图展开研究阻塞和等待那是性能调优最有趣也最费脑的方向。希望这份总结能帮到你少走一段我当年走过的弯路。本文还有配套的精品资源点击获取