Sql-server – Make database writeable (Azure MS Sql)

azure-sql-databasesql server

I have a bacpac file from a read-only database hosted on Azure. When imported to a new server the database is also read-only.

I’ve tried looking for answers with no luck.

How can I change it to a writeable?

Best Answer

It doesn't look like there are any supported ways to change this. However...

I noticed that the bacpac file is just a zip archive, so I unzipped it. The basic structure of the folder looks like this:

screenshot of bacpac folder contents

I then used KDiff to diff two bacpacs of the exact same data - one that was read-write, and one that was read-only. The main difference of substance was in model.xml:

screenshot of kdiff showing model.xml differences

So you should be able to remove those two lines* from the bacpac, and then import it, and it will be read-write! I attempted this on my local Developer Edition copy of SQL Server 2016, but got this error message:

Could not load schema model from package. (Microsoft.SqlServer.Dac)

The calculated checksum for model.xml in the package C:\Users\username\Desktop\my_database\my_database.bacpac is different from the saved checksum. (Microsoft.Data.Tools.Schema.Sql)

Which led me to this blog post: Azure SQL Database Support - Editing a .bacpac file

There are times when for whatever reason you just wish you could make a change to a .bacpac file before you import it into Azure.

So I followed the instructions there, running this PowerShell script to generate a new checksum:

$modelXmlPath = Read-Host "model.xml file path"

$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") }

$fileStream.Close()

$hashString

Then updating the Origin.xml checksum line at the end:

<Checksums>
    <Checksum Uri="/model.xml">long checksum number goes here</Checksum>
</Checksums>

Zipped all the files again (I used the "Deflate" algorithm in 7-zip), and then imported the database. It came in perfectly as a normal, read-write database.

*Note: I didn't explicitly enable snapshot isolation, that happens automatically when you set a database to read-only. Not even Paul White is sure why, so what hope do the rest of us have? For more, see Snapshot_isolation_state_desc on a Read only/Stand by Database

Related Question