（5.3.10）数据库迁移——sql server降级操作

sql server如果要把高版本数据 还原到低版本，需要一些方法和策略

【0】需求2008R2降低到2005

【1】使用生成脚本功能导出所有数据库对象及表数据（2008以上）

　　

　　

【2】使用 生成脚本功能导出表结构+链接服务器插入数据

　　（1）获取表结构脚本

　　　　

 　　（2）构造链接服务器，生成插入脚本

--要使用，@new_db内容用查找替换来全部替换掉，否则会有问题的

declare @old_db varchar(100),@new_db varchar(100),@sql varchar(max)
select @old_db='[10.1.4.234].db_tank',@new_db='test2'

-- 获取有自增列插入语句
select 'use '+@new_db+';set identity_insert '+t1.name+' on;insert into '+@new_db+'.dbo.'+t1.name+'('+
(select stuff((select',['+name+']' from test2.sys.syscolumns where id=t1.object_id for xml path('')),1,1,''))+')'+
' select '+(select stuff((select',['+name+']' from test2.sys.syscolumns where id=t1.object_id for xml path('')),1,1,''))
+' from '+@old_db+'.dbo.'+t1.name+';set identity_insert '+t1.name+' off;' 
from test2.sys.tables t1 
where exists 
(
select 1 from test2.sys.columns t2 where t2.is_identity=1 and t1.object_id=t2.object_id 
) 
and t1.type='u'


-- 获取无自增列插入语句 

select 'use '+@new_db+';insert into '+@new_db+'.dbo.'+t1.name+' select * from '+@old_db+'.dbo.'+t1.name+';' 
from test2.sys.tables t1 
where not exists 
(
select 1 from test2.sys.columns t2 where t2.is_identity=1 and t1.object_id=t2.object_id 
) 
and t1.type='u'

--------------核验--------------------
-- 更新统计信息
use test2
EXEC sys.sp_updatestats

use db_logs
EXEC sys.sp_updatestats
-- 根据聚集索引/堆对比行数

use master;
if object_id('temp1') is not null
drop table master.dbo.temp1

select t2.name tab_name,rows,indid into master..temp1 
from [10.1.4.234].db_tank.sys.sysindexes t1 join [10.1.4.234].db_tank.sys.sysobjects t2 on t1.id=t2.id 
where t1.indid in(0,1) and t2.type!='S'

use master;
if object_id('temp2') is not null
drop table master.dbo.temp2

select t2.name tab_name,rows,indid into master..temp2 
from test2.sys.sysindexes t1 join test2.sys.sysobjects t2 on t1.id=t2.id 
where t1.indid in(0,1) and t2.type!='S'

 
-- 根据聚集索引/堆对比行数，筛选行数不一致的
select t1.*,t2.*,t1.rows-t2.rows as flag from 
master..temp1 t1 join
master..temp2 t2 on t1.tab_name=t2.tab_name
where t1.rows-t2.rows>0
-- 对比表存在情况
select t1.*,t2.*,t1.rows-t2.rows as flag from 
master..temp1 t1 full join
master..temp2 t2 on t1.tab_name=t2.tab_name
where t1.tab_name is null or t2.tab_name is null

【3】BCP（不用考虑identity）

简单思路就是

（1）生成表结构脚本到低版本数据库

（2）高版本BCP导出

（3）低版本BCP导入

参考：BCP导入导出

--打开高级选项
EXEC SP_CONFIGURE 'show advanced options', 1;
RECONFIGURE;
--启用执行CMD命令
EXEC SP_CONFIGURE 'xp_cmdshell', 1;
RECONFIGURE;

--导出
EXEC [master]..xp_cmdshell 'BCP db_tank..sys_users_detail out D:DataBackUpsys_users_detail.txt -c  -Uroadadmin -Ppo@#r53e%$k8(+-3 -S127.0.0.1,2433'
GO

select COUNT(1) from db_tank..sys_users_detail

--导入
use test2 
go
EXEC [master]..xp_cmdshell
'BCP test2.dbo.sys_users_detail in D:DataBackUpsys_users_detail.txt -c -T -Uroadadmin -Ppo@#r53e%$k8(+-3 -S127.0.0.1,2433'
GO

【4】使用发布订阅

这个百度发布订阅使用教程就是，这里不做演示了。

【必看】

不管用哪种方法，记得同步登录名，权限

-- 登录名获取
SELECT  'CREATE LOGIN [' + p.name + '] '
       + CASE WHEN p.type IN ( 'U', 'G' ) THEN 'FROM windows '
              ELSE ''
         END + 'WITH ' + CASE WHEN p.type = 'S'
                              THEN 'password = '
                                   + master.sys.fn_varbintohexstr(l.password_hash)
                                   + ' hashed, ' + 'sid = '
                                   + master.sys.fn_varbintohexstr(l.sid)
                                   + ', check_expiration = '
                                   + CASE WHEN l.is_expiration_checked > 0
                                          THEN 'ON, '
                                          ELSE 'OFF, '
                                     END + 'check_policy = '
                                   + CASE WHEN l.is_policy_checked > 0
                                          THEN 'ON, '
                                          ELSE 'OFF, '
                                     END
                                   + CASE WHEN l.credential_id > 0
                                          THEN 'credential = ' + c.name
                                               + ', '
                                          ELSE ''
                                     END
                              ELSE ''
                         END + 'default_database = '
       + p.default_database_name
       + CASE WHEN LEN(p.default_language_name) > 0
              THEN ', default_language = ' + p.default_language_name
              ELSE ''
         END
FROM    sys.server_principals p
       LEFT JOIN sys.sql_logins l
       ON p.principal_id = l.principal_id
       LEFT JOIN sys.credentials c
       ON l.credential_id = c.credential_id
WHERE   p.type IN ( 'S', 'U', 'G' )
       --AND p.name NOT IN ( 'sa')
               AND p.name NOT LIKE '%##%'
               AND p.name NOT LIKE '%NT SERVICE%'
               AND p.name NOT LIKE '%NT AUTHORITY%'