请选择 进入手机版 | 继续访问电脑版
搜索
房产
装修
汽车
婚嫁
健康
理财
旅游
美食
跳蚤
二手房
租房
招聘
二手车
教育
茶座
我要买房
买东西
装修家居
交友
职场
生活
网购
亲子
情感
龙城车友
找美食
谈婚论嫁
美女
兴趣
八卦
宠物
手机

Sqlserver数据迁移踩坑记

[复制链接]
查看: 102|回复: 0

2万

主题

2万

帖子

7万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
70839
发表于 2020-1-14 14:14 | 显示全部楼层 |阅读模式
一. 背景
老的库source_db是安装在window server 2003上, 新库target_db为华为云的SqlServer 2008(RDS),  老库数据迁移到新库上。
二. 迁移步伐
1. 为了保证在迁移进程中源库数据不再被更新,先将库设备为只读。
方式1:可以操纵以下命令来设备sql server数据库的只读特征。

  1. USE [zssg]GOALTER DATABASE [TESTDB] SET READ_ONLY WITH NO_WAITGO
复制代码
方式2:也可以可视化界面设备(Sqlserver managament studio 2008), 右键数据库 -> 属性,进入以下页面
我的关键词 Sqlserver数据迁移踩坑记  热门消息 1456105-20200114104850298-1272029014



2. 源库导出数据到方针库
右键数据库 -> 使命 -> 导出数据, 以下图:
我的关键词 Sqlserver数据迁移踩坑记  热门消息 1456105-20200114105710831-2116814532




间接下一步
我的关键词 Sqlserver数据迁移踩坑记  热门消息 1456105-20200114105845402-732511597



源库的办事器称号会自动带出来, 也可以本身手动编辑(IP,端口)的格式, 比如:  192.168.1.20,1433。
输入账户名密码后,点击“革新”, 挑选数据库, 然后点击“下一步”。
我的关键词 Sqlserver数据迁移踩坑记  热门消息 1456105-20200114105949162-517123078




填写方针库的信息, 然后下一步。
我的关键词 Sqlserver数据迁移踩坑记  热门消息 1456105-20200114110320724-1188756379




我的关键词 Sqlserver数据迁移踩坑记  热门消息 1456105-20200114110444237-507307812


全选表和视图, 然后点击“编辑映照”
我的关键词 Sqlserver数据迁移踩坑记  热门消息 1456105-20200114110729085-395188608




立即运转,以下:
我的关键词 Sqlserver数据迁移踩坑记  热门消息 1456105-20200114110942434-2109676311




点击“完成”, 起头迁移
我的关键词 Sqlserver数据迁移踩坑记  热门消息 1456105-20200114111032576-1157109897




迁移乐成,以下:
我的关键词 Sqlserver数据迁移踩坑记  热门消息 1456105-20200114111057784-288677791




进入我们方针库,发现迁移过来的表结构, 丧失了全数的表主键、索引、自增序列。 故我们必要举行再处置赏罚。
三、迁移后表结构处置赏罚
处置赏罚逻辑:
1. 方针库地址办事器再新建一个库(命名为mid_db), 把源库的表结构(不包含数据)拷贝过来(可以经过navicat的数据传输,以下图)
我的关键词 Sqlserver数据迁移踩坑记  热门消息 1456105-20200114112105340-714485943



2. 点击tab中的“高级”,以下设备:(表选项只必要勾选“包含自动递增”, 索引等那些先不要加,否则做数据插入会很慢!), 然后点击右下角的“起头”, 举行表结构迁移到mid_db库
我的关键词 Sqlserver数据迁移踩坑记  热门消息 1456105-20200114112204364-166080264




3. 拷贝表结构终了, 以下图:
我的关键词 Sqlserver数据迁移踩坑记  热门消息 1456105-20200114112530199-1813921260




4. 对mid_db库的全数表举行重命名(以下为构建重命名的sql, 表名增加同一的前缀newfix_)
  1. select 'exec sp_rename ''' + name + ''',''newfix_' + name + ''';' from sys.tables;
复制代码
实行查询成果的sql, 重命名终了。
5. 把mid_db的全数表结构拷贝到target_db中。(方式为同上 3.1 - 3.4 步伐)

6. 构建insert语句, 把target表的不带newfix_前缀的表数据拷贝到带前缀newfix_的表
  1. -- 有自增字段的select name,'set identity_insert newfix_' +name+' ON; insert into newfix_'+name+ '('+columns+ ') select '+columns+ ' from '+ name+ ' where 1=1;'+'set identity_insert newfix_' +name+' OFF;' from (SELECT a.name,          columns = stuff((                 SELECT ',[' + b.name + ']'                   FROM sys.columns B join sys.tables c on B.object_id = c.object_id where  c.name = a.name                    FOR xml path('')) , 1 , 1 , '')    FROM sys.tables A where 1=1 and a.name not like 'newfix_%' group by a.name)aa where exists(select 1 from syscolumns where id=object_id('newfix_' + aa.name) and status=0x80) order by aa.name;select *from sys.tables A where 1=1 and a.name not like 'newfix_%'-- 没有自增字段的select name,'insert into newfix_'+name+ '('+columns+ ') select '+columns+ ' from '+ name+ ' where 1=1;' from (SELECT a.name,          columns = stuff((                 SELECT ',[' + b.name + ']'                   FROM sys.columns B join sys.tables c on B.object_id = c.object_id where  c.name = a.name                    FOR xml path('')) , 1 , 1 , '')    FROM sys.tables A where 1=1 and a.name not like 'newfix_%' group by a.name)aa where not exists(select 1 from syscolumns where id=object_id('newfix_' + aa.name) and status=0x80) order by aa.name;
复制代码

实行上面查询成果的SQL, 乐成把数据插入到有自增机制的带newfix_前缀的新表中。

7. 把target_db的全数不带newfix_的表重命名为 oldfix_, 把全数带newfix_表重命名为去掉newfix_的表
  1. select 'exec sp_rename ''' + name + ''',''oldfix_' + name + ''';' from sys.tables where name not like 'newfix_%';select 'exec sp_rename ''' + name + ''',''' + REPLACE (name, 'newfix_','') + ''';' from sys.tables where name like 'newfix_%';
复制代码
实行如上的查询成果, 实现了表的替换。

8. 查询source_db的全数索引,用于构建target_db库的全数表的索引。
  1. BEGIN    WITH tx AS (        SELECT            a.object_id,            b.name AS schema_name,            a.name AS table_name,            c.name AS ix_name,            c.is_unique AS ix_unique,            c.type_desc AS ix_type_desc,            d.index_column_id,            d.is_included_column,            e.name AS column_name,            f.name AS fg_name,            d.is_descending_key AS is_descending_key,            c.is_primary_key,            c.is_unique_constraint        FROM            sys.tables AS a        INNER JOIN sys.schemas AS b ON a.schema_id = b.schema_id        AND a.is_ms_shipped = 0        INNER JOIN sys.indexes AS c ON a.object_id = c.object_id        INNER JOIN sys.index_columns AS d ON d.object_id = c.object_id        AND d.index_id = c.index_id        INNER JOIN sys.columns AS e ON e.object_id = d.object_id        AND e.column_id = d.column_id        INNER JOIN sys.data_spaces AS f ON f.data_space_id = c.data_space_id    ) SELECT        Drop_Index = CASE    WHEN (        a.is_primary_key = 1        OR a.is_unique_constraint = 1    ) THEN        'ALTER TABLE ' + a.table_name + ' DROP CONSTRAINT ' + a.ix_name + ';'    ELSE        'DROP INDEX ' + a.ix_name COLLATE SQL_Latin1_General_CP1_CI_AS + ' ON ' + a.schema_name + '.' + a.table_name + ';'    END,    Create_Index = CASEWHEN (    a.is_primary_key = 1    OR a.is_unique_constraint = 1) THEN    'ALTER TABLE ' + a.table_name + ' ADD CONSTRAINT ' + a.ix_name + CASEWHEN a.is_primary_key = 1 THEN    ' PRIMARY KEY'ELSE    ' UNIQUE'END + '(' + indexColumns.ix_index_column_name + ');'ELSE    'CREATE ' + CASEWHEN a.ix_unique = 1 THEN    'UNIQUE 'ELSE    ''END + a.ix_type_desc + ' INDEX ' + a.ix_name COLLATE SQL_Latin1_General_CP1_CI_AS + ' ON ' + a.schema_name + '.' + a.table_name + '(' + indexColumns.ix_index_column_name + ')' + CASEWHEN IncludeIndex.ix_included_column_name IS NOT NULL THEN    ' INCLUDE (' + IncludeIndex.ix_included_column_name + ')'ELSE    ''END + ' ON [' + a.fg_name + '];'END, CASEWHEN a.ix_unique = 1 THEN    'UNIQUE'END AS ix_unique, a.ix_type_desc, a.ix_name, a.schema_name, a.table_name, indexColumns.ix_index_column_name, IncludeIndex.ix_included_column_name, a.fg_name, a.is_primary_key, a.is_unique_constraintFROM    (        SELECT DISTINCT            ix_unique,            ix_type_desc,            ix_name,            schema_name,            table_name,            fg_name,            is_primary_key,            is_unique_constraint        FROM            tx    ) AS a OUTER APPLY (        SELECT            ix_index_column_name = STUFF(                (                    SELECT                        ',' + column_name + CASE                    WHEN is_descending_key = 1 THEN                        ' DESC'                    ELSE                        ''                    END                    FROM                        tx AS b                    WHERE                        schema_name = a.schema_name                    AND table_name = a.table_name                    AND ix_name = a.ix_name                    AND ix_type_desc = a.ix_type_desc                    AND fg_name = a.fg_name                    AND is_included_column = 0                    ORDER BY                        index_column_id FOR XML PATH ('')                ),                1,                1,                ''            )    ) IndexColumns OUTER APPLY (        SELECT            ix_included_column_name = STUFF(                (                    SELECT                        ',' + column_name                    FROM                        tx AS b                    WHERE                        schema_name = a.schema_name                    AND table_name = a.table_name                    AND ix_name = a.ix_name                    AND ix_type_desc = a.ix_type_desc                    AND fg_name = a.fg_name                    AND is_included_column = 1                    ORDER BY                        index_column_id FOR XML PATH ('')                ),                1,                1,                ''            )    ) IncludeIndex where 1=1ORDER BY    a.schema_name,    a.table_name,    a.ix_nameEND
复制代码
取Create_Index列数据实行
我的关键词 Sqlserver数据迁移踩坑记  热门消息 1456105-20200114114050189-678980949



实行终了后,则全数索引建立终了。
9. 删除无用的中心表 oldfix_前缀的。
  1. select 'drop table ' + name + ';' from sys.tables where name like 'oldfix_%';
复制代码
10. 删除无用的中心库 mid_db
  1. drop database mid_db;
复制代码

其他的函数、视图和触发器可举行零丁迁移(操纵如上的SSMS大要navicat都可以), 不再赘述。

四、总结
1. 采纳中心库mid_db的原因原由是源库source_db没法一步到位迁移乐成到target_db, 由于SSMS工具做数据迁移后, 新库的表索引和自增序列等信息会丧失, mid_db和target_db的区分是多了自增属性。(target_db表不能间接点窜字段为自增, 必须先drop column后 add column ,会致使数据丧失,故我们采纳表数据拷贝的方式,避免数据丧失)
2. target_db的表在插入数据之前, 保证表是没有索引的, 否则会奇慢非常(频仍插入致使表索引字段记录不停重排), 插入终了后再建立索引。
3. SSMS工具对同一个机子的差别数据库间做数据迁移速度也很慢, 故倒霉用SSMS举行 target_db到mid_db的数据迁移, 而是把mid_db表拷贝到target_db, 然后在target_db及第行insert的表数据拷贝。

免责声明:假如加害了您的权益,请联系站长,我们会实时删除侵权内容,感谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Copyright © 2006-2014 淄博新闻网-淄博日报 淄博晚报 淄博财经新报 掌中淄博 淄博专业新闻资讯发布网站 版权所有 法律顾问:高律师 客服电话:0791-88289918
技术支持:迪恩网络科技公司  Powered by Discuz! X3.2
快速回复 返回顶部 返回列表