一个不走寻常路线的mssql对mssql的数据库迁移

一个不走寻常路线的mssql对mssql的数据库迁移

  • 通常做法
  • 碰到一点小需求
  • 实操
    • 链接服务器
    • 手动建立新的数据库
    • 复制数据库表结构
    • 复制表上的索引定义
    • 复制存储过程等相关内容
    • 复制需要保留的数据

通常做法

通常，我们做数据库迁移，都是使用数据库备份/还原方式来做的。一般来说，这样是最好的办法，大致如下几个步骤

1、对原有数据库进行备份
2、在新数据库服务器上对备份文件进行下载
3、在新数据库服务器上对备份文件进行还原
4、修正一些登录名、权限之类的设置

CSDN 文盲老顾的博客，https://blog.csdn.net/superwfei
老顾的个人社区，https://bbs.csdn.net/forums/bfba6c5031e64c13aa7c60eebe858a5f?category=10003&typeId=3364713

碰到一点小需求

可最近老顾碰到一个小问题，需要将几百GB的数据库迁移上云，迁移过程中，还要筛选掉一些历史数据不进行迁移。。。。。

这样，对于使用备份迁移的方式就有点不太适用了，于是老顾琢磨了一个偷懒的办法。

1、使用链接服务器，对指定的数据库进行远程连接
2、使用系统表复制指定的数据库的表结构
3、使用系统表复制相关的存储过程、触发器等一系列内容
4、使用系统表复制所有的索引
5、对相关数据进行条件筛选，并将符合条件的数据插入到新数据库

其实说起来，也并不能偷懒，但是，老顾的原始数据库服务器和新的云服务器的带宽设置的都很低，而且云服务器的硬盘大小设置的也很小，所以，也懒得弄备份还原了。那么就开始进行实操把。

实操

链接服务器

这个在网上一搜一大把，具体就不细说了，直接放指令

EXEC master.dbo.sp_addlinkedserver @server = N'db175', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'源数据库服务器IP'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'db175',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'db175',@useself=N'False',@locallogin=N'本地登录名',@rmtuser=N'原数据库登录名',@rmtpassword='原数据库登录密码'

db175 就是我为链接服务器进行的命名，后边很多指令都会用到哦。

小知识
mssql server 对与表名的完整定义是 {([链接服务器.]数据库名.)架构名.}表名
通过修改连接服务器名、数据库名，我们可以方便的进行跨服务器、跨数据库操作。

手动建立新的数据库

这个没办法，需要指定数据库的迁移，那么这个指定的过程是不可省略的，自行用 ui 操作，或者用指令 create database 操作就好。

复制数据库表结构

到这里，就该开始系统表的表演了

use 本地数据库名

declare @id int,@name varchar(100),@sql nvarchar(max)

declare cur cursor local for
select id,name 
from db175.远程数据库名.dbo.sysobjects 
where xtype='u' 
order by name

open cur
fetch next from cur into @id,@name
while @@fetch_status = 0
	begin
		--print @id
		--print @name
		if object_id(@name) is null
			begin
				select @sql = 'create table ' + @name + '(' + char(10) + char(9) + stuff((
					select char(10) + char(9) + ',[' + c.name + '] ' + t.name 
							+ (case 
								when t.name in ('int','date','datetime','uniqueidentifier','bit','bigint','float','money','smalldatetime','xml','text','ntext','tinyint') then '' 
								when t.name in ('decimal','numeric') then '(' + convert(varchar,col.length) + ',' + convert(varchar,c.scale) + ')'
								when col.length=-1 then '(max)' 
								else '(' + convert(varchar,col.length) + ')' end)
							+ (case when c.is_identity=1 then ' identity' else '' end)
							+ (case when col.isnullable=0 then ' not null' else '' end)
							+ (case when k.id is null then '' else ' primary key' end)
							+ (case when s.text is null then '' else ' default ' + s.text end)
					from db175.远程数据库名.dbo.syscolumns col
					left join db175.远程数据库名.dbo.syscomments s on col.cdefault=s.id
					left join db175.远程数据库名.dbo.systypes t on col.xtype=t.xusertype
					left join db175.远程数据库名.dbo.sysobjects pk on pk.parent_obj=col.id and pk.xtype='pk'
					left join db175.远程数据库名.dbo.sysindexes i on i.id=col.id and i.name=pk.name
					left join db175.远程数据库名.dbo.sysindexkeys k on i.id=k.id and i.indid=k.indid and col.colid=k.colid
					left join db175.远程数据库名.sys.columns c on c.object_id=col.id and c.name=col.name
					where col.id=@id 
					and o.name not like 'temp%' -- 过滤掉垃圾表、临时表之类的。。。。
					order by colorder
					for xml path('')
				),1,3,'') + ')'

				print(@sql)

				exec(@sql)

			end
		fetch next from cur into @id,@name
	end

close cur
deallocate cur

这一步老顾做的其实并不太完善，因为老顾自己的数据库中基本没有外键的设置，所以没有对外键和主键进行识别，一律当主键处理了，有需要的小伙伴自行完善一下就可以了，另外，如果有外键，那么建表顺序也要相应的调整一下，避免因为需要引用的列不存在出现建表失败的问题。

思路说明
1、通过 sysobjects 这个系统表，获取 xtype 为 U 的所有数据，这个数据就是所有的用户建立的表了
2、通过游标对表名称进行遍历，用来顺序生成新的表结构，如果需要调整生成顺序，在游标的 order 部分进行调整即可
3、使用 syscolumns 系统表，对每个表中所有的列进行遍历
4、使用 systypes 系统表，对每个列的 xtype 进行解析
5、通过 sysobjects 的 xtype 为 pk 的数据，判定当前表是否具有主键，嗯，父对象（parent_obj）为当前表
6、如果具有主键，则通过 sysindexes 和 sysindexkeys 对列名进行验证，判定具体哪个列为主键
7、结合系统视图 sys.columns 的浮点长度，根据列的 xtype 生成列类型定义
8、通过 syscomments 或者每个具有默认值的列的默认值设置
9、最后，通过 for xml 拼接除一个完整的 create table 指令，并执行

表结构复制就是这么简单，不用每个表上点一下右键=>编写表脚本为=>create 到 => 新编辑窗口，然后在一个表结构一个表结构的复制到新数据库执行了 ^v^。

复制表上的索引定义

use 本地数据库名

declare @sql nvarchar(max)

;with t as (
	select o.name tb,i.name idx,c.name col,is_included_column isInc,index_column_id sort,si.type,si.is_unique
	from db175.远程数据库名.sys.index_columns ic
	left join db175.远程数据库名.dbo.sysobjects o on ic.object_id=o.id
	left join db175.远程数据库名.dbo.syscolumns c on ic.object_id=c.id and ic.column_id=c.colid
	left join db175.远程数据库名.dbo.sysindexes i on i.id=ic.object_id and i.indid=ic.index_id
	left join db175.远程数据库名.dbo.sysobjects idx on idx.parent_obj=o.id and idx.name=i.name and idx.xtype='pk'
	left join db175.远程数据库名.sys.indexes si on si.object_id=o.id and si.index_id=ic.index_id
	where o.xtype='u' and idx.id is null
),t1 as (
	select distinct tb,idx,type,is_unique
	from t
),t2 as (
	select *
		,'create' 
			+ (case when is_unique=1 then ' UNIQUE' else '' end)
			+ (case when type=1 then ' CLUSTERED' when type=2 then ' NONCLUSTERED' when type=3 then ' CLUSTERED' else ' XXX-' + convert(varchar,type) + '-XXX' end)
			+ ' index [' + idx + ']'
			+ ' on [' + tb + ']('
			+ cols
			+ ')'
			+ (case when incs is null then '' else ' INCLUDE(' + incs + ')' end) cmd
	from t1 a
	cross apply (
		select stuff((
			select ',[' + col + '] asc'
			from t
			where tb=a.tb and idx=a.idx and isInc=0
			order by sort
			for xml path('')
		),1,1,'') cols,stuff((
			select ',[' + col + ']'
			from t
			where tb=a.tb and idx=a.idx and isInc=1
			order by sort
			for xml path('')
		),1,1,'') incs
	) b
)
select tb,idx,cmd
into #t 
from t2

select * from #t

declare cur cursor local for
select cmd
from #t
order by tb,idx

open cur
fetch next from cur into @sql
while @@fetch_status=0
	begin

		print(@sql)

		exec(@sql)

		fetch next from cur into @sql
	end

close cur
deallocate cur

drop table #t

思路说明
1、通过系统视图 sys.index_columns 来判断哪些列参与了索引，列的顺序及位置
2、关联到系统表 sysobjects ，获取表名
3、关联到系统表 syscolumns， 获取列名
4、关联到系统表 sysindexes，获取索引名
5、通过 sysobjects 和 sys.indexes 排除掉主键（建表时，主键自动创建索引）
6、通过 for xml ，对身退功成索引指令进行拼接，并将结果写入到临时表
7、通过游标遍历临时表，并执行构造好的生成索引指令

这里使用临时表的主要原因是，游标不支持 CTE 。。。。

当然，老顾这里的指令也是不完善的，只考虑了聚集索引和非聚集索引（type in (2,3)），其他索引类型老顾没用到，所以用 xxx 代替了，有需要的小伙伴自行补全相关内容即可。

这么操作一下，索引也完全复制过来了，不用再害怕遗漏了。

复制存储过程等相关内容

use 本地数据库名

declare @sql nvarchar(max)

declare cur cursor local for
select cmd 
from db175.远程数据库名.dbo.sysobjects o
cross apply (
	select (
		select '' + text 
		from db175.远程数据库名.dbo.syscomments
		where id=o.id
		order by colid
		for xml path(''),type
	).value('.','nvarchar(max)') cmd
) b
where xtype in ('tr','p','fn','fs','ft','tf','if') and object_id(o.name) is null and cmd is not null
order by xtype,name


open cur

fetch next from cur into @sql
while @@fetch_status=0
	begin
		
		exec(@sql)

		fetch next from cur into @sql
	end
close cur
deallocate cur

思路说明
1、通过 sysobjects 拿到所有存储过程、自定义函数、触发器之类的东西
2、通过 syscomments 拿到所有的指令文本，并将多行文本进行拼接成一个大文本
3、通过游标对生成的文本进行执行

这个就太简单一点了，只需要对 sysobjects 的 xtype 有了解，知道自己的库内用到了什么即可。比如 p 就是存储过程，tr 就是触发器，函数类型包括了 fn、fs等等。而syscomments 则保存了所有的相关建立指令，不过如果文本比较大，会分成多行。

如果有需要复制视图的情况，只需要把 xtype 里追加一个 V 就可以了。

最后，需要注意的是，老顾没有把程序集的复制放出来，毕竟clr设置相关的内容也很多很麻烦。再有一个，调用 clr 的自定义函数，在 syscomments 里没有文本，老顾暂时没有找到这个文本的存放位置，或者。。。不用文本，按照格式自己拼接？

另外就是 service broker 相关的定义，暂时没有找到相关系统表中的定义，后续有机会再补上。

复制需要保留的数据

啊，到了这一步，数据库大多数内容基本就完事了。

insert 表
select * 
from db175.远程数据库名.dbo.表
where 条件

如果需要连自增列的值都保留，自行设置下

set identity_insert 表 on

记得插入完了别忘记设置为 off

如果想偷懒，也可以遍历所有的表，然后自行生成一个插入数据的指令，不过老顾在这里就不再继续了，看看小伙伴能不能自行完成完整的数据迁移好了。