[個人筆記] 透過DAC把本機資料庫上傳至Windows Azure SQL Database (先前稱為 SQL Azure)

2012-11-16

2025
0
Azure
2012-11-20

前一篇的練習之後，今天做了DAC的研究，起因來自於我覺得這篇很有意思

http://www.dotblogs.com.tw/terrychuang/archive/2012/10/09/76365.aspx

照著做到匯出時看到熟悉的錯誤：

這不是在資料庫部署到SQL Azure時遇到的擴充屬性問題嗎…

用手動移除擴充屬性太辛苦了，所以找了方便的解決方案：

1.透過Script產生Script

--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'

 

--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'

2.在執行結果左上角按一下變成全選後在左上角按右鍵複製再開新查詢頁貼上執行(記得會有兩塊執行結果的Script要執行)

3.再次執行匯出就成功囉～

http://www.microsoft.com/zh-tw/download/details.aspx?id=34964

http://msdn.microsoft.com/zh-tw/library/ee210546.aspx

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/747cb7c2-b176-4f75-9d92-c5a9360b4ed3

http://msdn.microsoft.com/zh-tw/library/windowsazure/hh335292.aspx

http://sqldacexamples.codeplex.com/

回首頁