数据库SQL Server DAC 导入导出数据到SQL Azure问题

对Export data-tier application报错的处理 Error：SQL71564

这个问题是数据库中一些对象如MS_Description,MS_DiagramPane1不支持DAC Export Data-tier Appliation 的操作。

详见：DAC Support For SQL Server Objects and Versions http://msdn.microsoft.com/en-us/library/ee210549.aspx 这里有详细的说明。

遇到这个种问题一般的采用减轻问题的解决办法（Mitigation）

1. 首先选用支持DAC Extract Data-tier Application的数据库版本，如SQL Server 2008 R2 SP2或者更高版本。导出dacpac文件，这个文件包含了数据库结构，metadata和schema

2. 因为有的对象不支持DAC Export Data-tier Application，即导出bacpac文件。所以我们不能采取这种方式，我们采取微软提供的工具bcp.exe

3. 使用BCP.exe工具导出导入数据

1. 下载并安装Microsoft® ODBC Driver 11 for SQL Server® - Windows http://www.microsoft.com/zh-cn/download/details.aspx?id=36434
2. 下载并安装 Microsoft Command Line Utilities 11 for SQL Server http://www.microsoft.com/zh-CN/download/details.aspx?id=36433
3. 使用BCP工具从SQL Server导出数据

参考用例：C:\Users\ericwen>bcp ZcooTongDB.dbo.ZCT_SuperAdmins out "D:\DAC Packages\ ZcooTongDB.bacpac" -U sa -P *****

1. 使用BCP工具导入数据到SQL Azure

参考用例：C:\Users\ericwen>bcp ZcooTongDB1.dbo.ZCT_SuperAdmins in "D:\DAC Packages\ ZcooTongDB.bacpac" -U ericwen@[云数据库名称] -P ****** -S "[云数据库名称].database.windows.net"

bcp 实用工具：http://technet.microsoft.com/zh-cn/library/ms162802.aspx

在操作过程中可以用默认的选项，直接回车。如图：

如何实现批量导出数据呢，可以再SSMS运行以下脚本

SELECT 
      'bcp ' 
    + SCHEMA_NAME(schema_id) + '.' + name 
    + ' out ' 
    + ' D:\local_backup_directory\' + SCHEMA_NAME(schema_id) + '.' + name + '.txt' 
    + ' -c ' 
    + ' -S servername.database.windows.net ' 
    + ' -d database_name ' 
    + ' -U username ' 
    + ' -P password' 
FROM sys.tables;

将查询结果保存到bcpcmd.bat文件中，然后用cmd运行即可

4. 清除数据库中的Table、View、Column中的不支持DAC的对象（不支持Export 到 bacpac文件的对象）

在SSMS里运行

--tables 
  
select 'EXEC sp_dropextendedproperty 
@name = ''MS_Description'' 
,@level0type = ''schema'' 
,@level0name = ' + object_schema_name(extended_properties.major_id) + ' 
,@level1type = ''table'' 
,@level1name = [' + object_name(extended_properties.major_id)+']' 
from sys.extended_properties 
where extended_properties.class_desc = 'OBJECT_OR_COLUMN' 
and extended_properties.minor_id = 0 
and extended_properties.name = 'MS_Description' 
 

--views 
  
select 'EXEC sp_dropextendedproperty 
@name = '''+extended_properties.name+''' 
,@level0type = ''schema'' 
,@level0name = ' + object_schema_name(extended_properties.major_id) + ' 
,@level1type = ''view'' 
,@level1name = [' + object_name(extended_properties.major_id)+']' 
from sys.extended_properties 
where extended_properties.class_desc = 'OBJECT_OR_COLUMN' 
and extended_properties.minor_id = 0 
and (extended_properties.name = 'MS_DiagramPane1' or extended_properties.name = 'MS_DiagramPaneCount')

--columns 
  
select 'EXEC sp_dropextendedproperty 
@name = ''MS_Description'' 
,@level0type = ''schema'' 
,@level0name = ' + object_schema_name(extended_properties.major_id) + ' 
,@level1type = ''table'' 
,@level1name = ' + object_name(extended_properties.major_id) + ' 
,@level2type = ''column'' 
,@level2name = [' + columns.name+']' 
from sys.extended_properties 
join sys.columns 
on columns.object_id = extended_properties.major_id 
and columns.column_id = extended_properties.minor_id 
where extended_properties.class_desc = 'OBJECT_OR_COLUMN' 
and extended_properties.minor_id > 0 
and extended_properties.name = 'MS_Description' 

将运行后的结果再到SSMS里运行就可以移除那些不支持的对象了，这样再进行Export操作就可以成功。 

 

DAC相关文档：http://msdn.microsoft.com/en-us/library/ee210546.aspx