Sql Server 2005数据迁移备忘

项目中经常需要进行数据迁移, 这里的的迁移主要只指历史日志的迁移。

查询日志基本情况

--查询最早最晚的日志
SELECT * FROM [zlt-monitors].[dbo].[AlarmLogs] WHERE alarmLogId= (SELECT min(alarmLogId) FROM [zlt-monitors].[dbo].[AlarmLogs])

SELECT * FROM [zlt-monitors].[dbo].[AlarmLogs] WHERE alarmLogId= (SELECT max(alarmLogId) FROM [zlt-monitors].[dbo].[AlarmLogs])

SELECT * FROM [zlt-monitors].[dbo].[VarLogs] where id=( SELECT min( id) FROM [zlt-monitors].[dbo].[VarLogs])

SELECT * FROM [zlt-monitors].[dbo].[VarLogs] where id=( SELECT max( id) FROM [zlt-monitors].[dbo].[VarLogs])

--估算日志数量
select ( SELECT max( id) FROM [zlt-monitors].[dbo].[VarLogs]) - ( SELECT min( id) FROM [zlt-monitors].[dbo].[VarLogs])

--查看各个表空间占用情况
exec sp_MSForEachTable 
@precommand=N'create table ##(
表名 sysname,
记录数 int,
保留空间 Nvarchar(10),
使用空间 varchar(10),
索引使用空间 varchar(10),
未用空间 varchar(10))',
@command1=N'insert ## exec sp_spaceused ''?''',
@postcommand=N'select * from ## order by 记录数 desc;drop table ## '

日志导出

--直接导出表
bcp [zlt-monitors].[dbo].[AlarmLogs] out D:\db\alarmlog.txt -c -T

--按sql导出
bcp "select * from [zlt-monitors].[dbo].[VarLogs] where (recordTime between '2010-01-01 00:00:00' and '2010-02-01 00:00:00')" queryout  D:\db\varlog.2010-01.txt -c -T

--修正某些日期
bcp "SELECT [id],[varId],[varName],[varValue], DATEADD(d,36,[recordTime])  as recordTime,[synmark] FROM [zlt-monitors].[dbo].[VarLogs] where ([recordTime] between '2018-01-01' and '2018-01-29') " queryout  D:\db\varlog.20180206.20180306.txt -c -T

bcp "SELECT [alarmLogId],[alarmType],[associateId],DATEADD(d,37,[alarmTime]) as alarmTime,[alarmStuts],[alarmValue],[varName],[remark],[alarmLevel],[linkagePolicies] FROM [zlt-monitors].[dbo].[AlarmLogs] where ([alarmTime]  between '2018-01-01 00:00:00' and '2018-01-30 00:00:00')" queryout  D:\db\alarmlog.20180207.20180307.txt -c -T

日志导入

导出的文件是文本格式的, 并且没有列头, 所以字段需要对应好

bcp [zlt-monitors].[dbo].[VarLogs] in D:\db\varlog.2010-01.txt -c -T

日志清理

--清除表数据
truncate table Varlogs

设置外键层叠删除

alter table AlarmConfirm drop constraint FK_ALARMCON_ALARMCONF_ALARMLOG

alter table AlarmConfirm add constraint FK_ALARMCON_ALARMCONF_ALARMLOG foreign key (alarmLogId) references AlarmLogs(alarmLogId) on delete cascade