資料庫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