SQL Server How to change collation of existing Azure SQL database

9vw9lbht  于 2023-10-15  发布在  其他
关注(0)|答案(1)|浏览(117)

I want to change my existing SQL database's collation in Azure. I didn't find any instructions and been told that it is not possible. Is there any way to do that?

Tried to change it from SSMS via connection to Azure SQL database, but that didn't work.

ccrfmcuu

ccrfmcuu1#

You can change the collation at the time you create an Azure SQL Database.

On an existing database you can use the procedure explained here and shown below:

  1. Export the Azure SQL as bacpac
  2. Make you have a modern version of sqlpackage that support the /ModelFilePath parameter (we added it about a year ago).
  3. Open the .bacpac file using winzip or 7-zip
  4. Copy the model.xml to a local folder “C:\Temp\model.xml”
  5. Edit the “C:\Temp\model.xml” with the desired collation. For example change the value for "Property Name = Collation" to "Latin1_General_BIN".
  6. Run the import using sqlpackage.exe, but use the /ModelFilePath:C:\Temp\model.xml parameter to override the model.xml in the .bacpac. For example: sqlpackage.exe /Action:Import /tsn:[server].database.windows.net /tdn:[database] /tu:[user] /tp:[password] /sf:"C:\Temp\database.bacpac" /ModelFilePath:C:\Temp\model.xml
  7. Zip the files again
  8. Rename the zip to DBNAME.bacpac 9 - Do the import 10 - You can check the collation in the fields with this query.
SELECT T.name, C.name, C.collation_name
    FROM sys.tables T
    INNER JOIN sys.columns C
    ON T.object_id = C.object_id
    WHERE C.collation_name IS NOT NULL

相关问题