SQL SERVER从专家到小白

附加 & 分离 数据库

报错信息：
无法打开物理文件“D:\xxx.mdf”。操作系统错误 5:“5(拒绝访问。)”。
CREATE DATABASE 失败。无法创建列出的某些文件名。请查看相关错误。 (Microsoft SQL Server，错误: 5120)

解决方案：
修改数据库文件的权限​​。
右键单击“D:\xxx.mdf”文件（以及xxx.ldf日志文件），选择 ​​“属性”​​。
转到 ​​“安全”​​ 选项卡。点击 ​​“编辑…”​​ 按钮来更改权限。对所有用户勾选 ​​“完全控制”​​ 权限。点击 ​​“应用”​​ 和 ​​“确定”​​ 保存更改。

数据库复制

PS：注意表结构是否完全复制，可能出现 自增列等配置未复制。

复制表结构

选中数据库表后，右键选择导出表结构的脚本文件（.sql）。在新数据库中导入执行。
PS：若新数据库中已有该表，会被删除。

列出数据表名

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YourTableName';

设置表字段自增

sql server如何将表的字段设置为自增

新增包含自增字段记录

//启用手动插入自增列值，​​允许为表的自增 ID 字段显式插入值
SET IDENTITY_INSERT tableA ON;

//执行数据插入
INSERT INTO tableA (id, ...) VALUES (1, ...);  
INSERT INTO tableA (id, ...) SELECT id, ... FROM tableB;

//完成数据插入后，及时设置回 OFF 状态，恢复A表ID列的自动递增属性。
SET IDENTITY_INSERT tableA OFF;

执行数据插入：

1. 务必在语句中明确列出所有列名​​，包括自增的 ID 列。不指定列名的语句会执行失败。
2. 确保 B 表中准备插入的 ID 值在 A 表中​​不存在​​，否则会因违反主键或唯一约束而导致插入失败。如果 B 表的 ID 值与 A 表现有记录冲突，你需要先处理这些冲突的 ID（例如，通过修改 B 表中的 ID 值，但要注意外键关联）。

IDENTITY_INSERT

IDENTITY_INSERT 是针对某个表的临时设置，是一个会话级别的设置，只对当前数据库连接有效。它不是表的持久属性，也无法直接通过系统表或视图查询其当前状态。只能有一个表在同一数据库中开启IDENTITY_INSERT。

状态判断方案：
尝试插入带有标识列的值来判断是否开启。
如果没有报错，说明已开启；如果报错“当 SET IDENTITY_INSERT 为 OFF 时，不能在表 ‘xxx’ 中插入显式值”，说明未开启。

查询数据库中有自增字段的数据表

SELECT OBJECT_NAME(object_id) AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(object_id, 'TableHasIdentity') = 1

比较两张表差异

数据对比

EXCEPT运算符​​

SELECT ... FROM TableA EXCEPT SELECT ... FROM TableB

​​精确查找差异行​​。能直接返回在A表但不在B表的​​完整行数据​​。需双向使用（A EXCEPT B 和 B EXCEPT A）以找出所有差异。

PS：数据表中不能包含text类型。由于其特殊的存储方式和历史原因，无法直接参与这种等值比较，因此 SQL Server 会直接报错而不是尝试比较其内容。

JOIN操作​​

FULL OUTER JOIN, LEFT JOIN/IS NULL, INNER JOIN

​​灵活查找缺失或不同的记录​​。FULL JOIN可一次性找出双方独有的记录；INNER JOIN结合WHERE条件可找出匹配但内容不同的记录。

​​CHECKSUM校验​​

SELECT SUM(CHECKSUM(*)) FROM ...

​​快速一致性校验​​。计算整表的校验和，效率高，但仅适用于​​快速判断两张表是否完全一致​​，​​无法定位具体差异行​​。

​​tablediff工具​​

SQL Server命令行工具
​​微软官方工具，功能强大​​。无需编写SQL，可直接比较行数、数据内容，并生成差异脚本。尤其适用于复制拓扑中的数据一致性检查。

​​表结构对比​​

​​查询系统视图​​

INFORMATION_SCHEMA.COLUMNS, sys.columns

​​通过SQL查询获取列的详细信息（名称、数据类型、是否可为NULL等）并进行比对。

备份数据表

SELECT * INTO 新表名
FROM 原表名;

备份&恢复

打开【维护计划向导】时报错

报错信息：
代理 XP”组件已作为此服务器安全配置的一部分被关闭。系统管理员可以使用 sp_configure 来启用“代理 XP”。有关启用“代理 XP”的详细信息，请参阅 SQL Server 联机丛书中的“外围应用配置器”。

解决方案：
打开SqlServer配置管理器，找到“SQL Server 代理(MSSQLSERVER)”服务，然后启动该服务。

SQL Server 定时自动备份和自动删除方法图文超详细步骤