sqlserver历史资料清理

1.导出导入方式

1.1.导出全表

# -T 使用当前用户的Windows凭证进行身份验证
bcp "monkey.dbo.students" out "D:\owner\students.bcp" -N -T
# 使用用户名密码进行身份验证
bcp "monkey.dbo.students" out "D:\owner\students.bcp02" -N -U monkey -P monkey -S 127.0.0.1

1.2.导出要保留时间内的数据

bcp "SELECT * FROM monkey.dbo.students WHERE add_date >= '2024-03-20 00:00:00'" queryout "D:\owner\students_20240320.bcp" -N -T

1.3.truncate表

sqlcmd -Q "truncate table monkey.dbo.students;"

1.4.禁用trigger

DISABLE TRIGGER tri_students ON students;

1.5.导入

导入前先删除索引，导入后再建会加快导入进度

bcp monkey.dbo.students in "D:\owner\students_20240320.bcp" -T -N

1.6.启用trigger

ENABLE TRIGGER tri_students ON students;

2.重命名方式

2.1.停业务

如果可以，尽量停止业务后再作业

2.2.备份

为防止作业失败，作业前备份数据

bcp "monkey.dbo.students" out "D:\owner\students.bcp" -N -T
bcp "SELECT * FROM monkey.dbo.students WHERE add_date >= '2024-03-20 00:00:00'" queryout "D:\owner\students_20240320.bcp" -N -T

2.3.创建BAK新表

获取的脚本要完整保存下来

2.3.1.获取建表脚本

SMMS获取

dbeaver获取

2.3.2.新建BAK表

-- 只建表（MSSM里面default值在后面，记得加上），索引，主键等后续再加
CREATE TABLE monkey.dbo.students_bak (
	studentNo int NOT NULL,
	studentClass int NULL,
	studentName nchar(10) COLLATE Chinese_PRC_CI_AS NULL,
	add_date datetime DEFAULT getdate() NOT NULL
);

2.4.查看依赖关系

看表上是否有存储过程，触发器

2.5.保存trigger的脚本

-- 查看是否有trigger,is_disabled = 0表示启用，is_disabled = 1表示禁用
SELECT 
    name AS TriggerName,
    is_disabled AS IsDisabled
FROM 
    sys.triggers
WHERE 
    parent_id = OBJECT_ID(N'students');

2.6.保留数据存入BAK表

insert into students_bak SELECT * FROM students WHERE add_date >= '2024-03-20 00:00:00';

2.7.重命名表名索引名

2.7.1.重命名表名

EXEC sp_rename 'students', 'students_20240627';
EXEC sp_rename 'students_bak', 'students';

2.7.2.删除旧表主键

-- 查看主键
SELECT 
    t.name AS TableName,
    c.name AS ConstraintName
FROM 
    sys.tables t
INNER JOIN 
    sys.key_constraints c ON t.object_id = c.parent_object_id
WHERE 
    c.type = 'PK' AND t.name = 'students_20240627';
-- 删除主键
ALTER TABLE students_20240627 DROP CONSTRAINT PK_Students;

2.7.3.重命名旧表索引

-- 查看表上的索引
SELECT 
    i.name AS IndexName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.type_desc AS IndexType,
    ic.index_column_id AS ColumnID,
    c.name AS ColumnName
FROM 
    sys.indexes i
INNER JOIN 
    sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN 
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE 
    OBJECT_NAME(i.object_id) = 'students_20240627'
ORDER BY 
    TableName, IndexName, ic.index_column_id;
-- 重命名表上的索引
EXEC sp_rename 'students_20240627.ix_students_ad', 'ix_students_ad_bak', 'INDEX';

2.7.4.删除旧表trigger

-- 查看trigger
SELECT 
    name AS TriggerName,
    is_disabled AS IsDisabled
FROM 
    sys.triggers
WHERE 
    parent_id = OBJECT_ID('students_20240627');
-- 删除trigger    
DROP TRIGGER tri_students;

2.8.新建索引trigger

-- 新建索引
create index ix_students_ad on students(add_date);
-- 新建主键
ALTER TABLE students ADD CONSTRAINT PK_Students PRIMARY KEY (studentNo);
-- 新建trigger
CREATE TRIGGER tri_students ON students
AFTER INSERT
AS 
   insert
      into students_hist select inserted.*
   from inserted

2.9.重编译失效存储过程

-- 查看存储过程
SELECT 
    o.name AS Object_Name,
    o.type_desc
FROM 
    sys.sql_modules m
JOIN 
    sys.objects o ON m.object_id = o.object_id
WHERE 
    m.definition LIKE '%students%' 
    AND o.type IN ('P', 'TF'); 
-- 编译存储过程
EXEC sp_recompile 'TransferStudentsToHistory';

2.10.收集统计信息

UPDATE STATISTICS students;