SQLServer数据库生成脚本降级迁移（数据库脚本的主外键、自增量identity、数据库版本降级、超大sql文件处理）

·

SQLServer数据库生成脚本迁移（数据库脚本的主外键、自增量identity处理）

　　软件开发中，经常涉及到不同数据库（包括不同产品的不同版本）之间的数据结构与数据的导入导出。处理过程中会遇到很多问题，尤为突出重要的一个问题就是主从表之间，从表有外检约束，从而导致部分数据无法导入。

 

情景一、同一数据库产品，相同版本

　　此种情况下源数据库与目标数据库的数据结构与数据的导入导出非常简单。

方法1：备份源数据库，恢复到目标数据库即完成。（数据库右键，任务--备份，备份成.bak文件后在目标数据库恢复）

       

 

方法2：使用SQL Sever数据库自带的【复制数据库】功能或者【导入数据】功能按照向导操作即可。

        

 

方法3：直接拷贝数据库的 *.mdf 文件（ 一般是 DB名.mdf  和 DB名_log.mdf ）

           可能需要数据库离线后才能拷(比如分离后拷贝，拷贝完成后再附加数据库回来；或者直接停了数据库服务后拷贝)

          拷贝的数据库附加到目标SSMS即可。

 

情景二、同一数据库产品，不同版本

　　情形2.1、源数据库版本低，目标数据库版本高

                  此种情况处理方式同情景一。（参考上面的方法）

       情形2.2、源数据库版本高，目标数据库版本低

由于目标数据库版本低于源数据库，源数据库中产生的脚本架构无法兼容低版本，所以不能通过直接备份还原的方式来操作。

 

　　本文以SQL Server2016数据库为数据源、SQLServer2012为目标数据库为例，主要解决主从表之间、从表有外键和identity(自增)约束时，数据导入失败的问题。 操作过程分为以下几个步骤：

步骤1：从源数据库生成【不带外键关系】的数据结构脚本 

　　

 

  连接上源数据库所在实例后，右键点击源数据库,【任务】-->【生成脚本】

     

 

 

弹出“生成和发布脚本”对话框

   

点击【下一步】按钮，看到“选择对象”步骤

    不是生成数据部分时，可以选择“为整个数据库及所有数据库对象编写脚本(E)”

 

点击【下一步】按钮，弹出“设置脚本编写选项”

 

 

 

 点击【高级】按钮，弹出【高级脚本编写选项】设置窗口    （此步骤非常重要）

   A. 将“编写外键脚本”的值设置为false
      意思是这一步骤生成的数据结构脚本中不包含表之间的外键关系。其他选项根据实际情况设置。

  B. 脚本兼容性，“服务器版脚本”设置为兼容：Sql Server 2012  （这里是目标数据库的版本,可根据自己实际情况修改）

   点击【确定】按钮，退回到保存脚本界面：将脚本文件名另存为“01Original_Schema_WithoutFK_sql12.sql”。

 最后再点击【下一步】按钮，生成脚本。

 

步骤2：导入数据结构脚本至目标数据库

　　

 

   在目标服务器上新建目标数据库，命名同源数据库名（命名成其他名字的也可以）。      

不通过sql脚本生成数据库的原因：sql脚本不能创建文件夹,所以最好是先有了数据数据的存储位置。

  选中新建的数据库，打开步骤一中保存的”01Original_Schema_WithoutFK_sql12.sql“脚本文件，运行该文件，

  运行成功后，目标数据库中成功创建了表、视图、存储过程、自定义函数等。

 

步骤3：从源数据库创建数据脚本

 

 

  3.1 连接上源数据库所在实例后，右键点击源数据库,【任务】-->【生成脚本】；

  3.2 在生成脚本对话框，点击【下一步】按钮，进入“选择对象”步骤

       这一步是生成DB数据部分，所以不能选择“为整个数据库及所有数据库对象编写脚本(E)”；
       只需要选择所有的表即可。

   

 

 

 

   3.3 选中源数据库与所有的表。点击【下一步】按钮，进入“设置脚本编写选项”步骤
       在保存脚本位置：将脚本文件名另存为“01Original_Data_sql12.sql”。

  【高级】：

         前提：脚本兼容性，“服务器版脚本”设置为兼容：Sql Server 2012  （这里是目标数据库的版本,可根据自己实际情况修改）；

         “要编写脚本的数据的类型”：仅限数据    

     

     

 

【下一步】，生成数据部分的脚本“02Original_Data_sql12.sql”。

   

 

步骤4：导入数据脚本至目标数据库

 

 

在目标数据库中，执行 “02Original_Data_sql12.sql”数据脚本即可。

   注：对于数据库的一些表中主键或者其他设置为int类型，且设置自增长(identity)类型的列无需处理。
          因为上一步中最新版本的SSMS在生成脚本时，已经做了这些处理。

其他说明：

对于数据库的一些表中主键或者其他设置为int类型，且设置自增长类型的列，需要做以下处理：

 SET IDENTITY_INSERT [TableName] ON ;    --每个含自增量(identity)的表都需要做一次

一般字段如果是identity的，比如定义的时候nameid identity（1，1)就是说从1开始增长，每次加1，那么插入一条记录nameid字段是不需要手动赋值(一般也不允许）。那么有时候需要插入自定义值的时候，就设置set identity_insert on；就可以手动插入了。操作完数据插入后，再将其关闭。

选中目标数据库，并打开步骤3中保存的“02Original_Data_sql12.sql”数据脚本，运行之，成功后，查看数据表。

查询结果可以看出已经成功导入数据。最后再恢复设置：

  SET IDENTITY_INSERT  [TableName] Off ;

 

非常大的数据库怎么办？生成的sql脚本文件非常大无法在SSMS中打开怎么办？ 

从外网把数据库中的表结构以及数据用”导出脚本“的方式导出来（几G的样子），但是在本机执行sql脚本的时候，直接就是out of memory。可使用sqlcmd命令来完成这一工作。

sqlcmd命令

step1: 在查询分析器中执行下面的sql语句  

select @@servername ； --获得数据库服务器名称。

step2:通过运行指令执行脚本    

sqlcmd -S (local) -U sa -P ***** -d ECT -r -i 02Original_Data_sql12.sql

其中，(local)是我的本地数据库服务器，ECT是数据库名称，02Original_Data_sql12.sql是我的脚本文件。

  

 

 

osql命令

:: osql -S 服务器 -U 用户名 -P 密码 -i SQL路径 
osql -S (local) -U sa -P esd!5Life -i .\Backup_ECT_兼容2012sql.sql
:: //注意：1，命令行中，用户名密码等都不用带引号(带引号会当作是用户名的一部分)
:: 2，sql脚本中创建数据库所在的文件夹必须事先存在，否则会白忙乎！（亲身经历跑了8小时都是“1行受影响”结果最后一行是数据库不存在，悲催呀！）

 

 

步骤5：从源数据库生成仅包含表外键关系的数据结构脚本

 

 

　　步骤与步骤1大致相同，最后一步设置相反

红色框内，将“编写外键脚本”设置为True，其他选项与步骤1中设置相反。点击"确定"按钮，生成脚本，另存为“03Original_Schema_WithFK_sql12.sql”。

 

步骤6：导入外键结构关系脚本至目标数据库

 

 

 　　选中目标数据库，打开步骤5中保存的“03Original_Schema_WithFK_sql12.sql”脚本文件，运行之，运行成功后，查看表结构；可以看到，外键已经成功创建。

整个数据库迁移完成。。。

 

 

 

·

 SQLServer数据库生成脚本降级迁移(完美解决主外键、自增量identity、数据库版本降级、大sql文件)所有步骤简单汇总：

 1.生成脚本01Original_Schema_WithoutFK_sql12.sql

 2.执行脚本；

 3.生成脚本02Original_Data_sql12.sql

 4.执行脚本；

 5.生成脚本03Original_Schema_WithFK_sql12.sql

 6.执行脚本；

···

(identity)