Change collation of existing Azure SQL database

Another article I wrote to Azure SQL DB Support blog

There is no easy way at this moment to change an existing database collation. The workaround is to modify manually the BACPAC file

When you are creating a new database you can change the collation in the portal

Or else using TSQL (

But to change an existing DB, check the information below:

UPDATE 2019-02-06: Just got an information from the product group that there is an easier way to do this

“We added a /ModelFilePath command line parameter to sqlpackage to override the model.xml. This should simplify the process steps to avoid the re-compute the hash in the origin.xml, and re-compressing the .bacpac which results in corruption for large files.”

So the steps are:

1. Make sure you have a modern version of sqlpackage that support the /ModelFilePath parameter (we added it about a year ago).

  • Latest sqlpackage version is here.
  • Sqlpackage installs to the C:\Program Files\Microsoft SQL Server\150\DAC\bin directory.

2. Open the .bacpac file using winzip or 7-zip

3. Copy the model.xml to a local folder “C:\Temp\model.xml”

4. Edit the “C:\Temp\model.xml” with the desired collation. For example:

From: <Property Name="Collation" Value="SQL_Latin1_General_CP1_CI_AS" />
To: <Property Name="Collation" Value="Latin1_General_BIN" />

5. 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] /tdn:[database] /tu:[user] /tp:[password] /sf:"C:\Temp\database.bacpac" /ModelFilePath:C:\Temp\model.xml

*Until this moment (2019-03-27) this parameter is not a documented parameter at

Below you can find the old way, from the original post

1 – Export the database to .bacpac file

2 – Rename it to .ZIP

3 – Unzip in a folder, you are going to see files like below

4 – Alter the file model.XML, with the requested default collation for the database

From: <Property Name="Collation" Value="SQL_Latin1_General_CP1_CI_AS" />
To: <Property Name="Collation" Value="Latin1_General_BIN" />

5 – You can also check also fields that have non default collations

<Element Type="SqlSimpleColumn" Name="[dbo].[Table_1].[anothercollation]">
<Property Name="Collation" Value="SQL_Latin1_General_CP1_CS_AS" />

6 – Alter the file origin.xml

  • You are going to find a checksum like the below
<Checksum Uri="/model.xml">35EF75437D2087E0FB029E5D726B229B973CFB0EFE75D014E74214C468976D51</Checksum>
  • You need to recalculate it using a Powershell script. Make sure to change the file path
$modelXmlPath = "C:\FILEPATH\model.xml"
$hasher = [System.Security.Cryptography.HashAlgorithm]::Create("System.Security.Cryptography.SHA256CryptoServiceProvider")
$fileStream = new-object System.IO.FileStream -ArgumentList @($modelXmlPath, [System.IO.FileMode]::Open)
$hash = $hasher.ComputeHash($fileStream)
$hashString = ""
Foreach ($b in $hash) { $hashString += $b.ToString("X2") }
  • Change the file origin.xml with the new hash and save it

7 – Zip the files again

  • Make sure not to zip the folder where you unzipped the bacpac, only the files inside it

8 – Rename the zip to DBNAME.bacpac

9 – Do the import

10 – You can check the collation in the fields with this query

SELECT,, 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

I used as a reference the information from this other article


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s