2

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?

  • 1
    Can you change the attribute on the bacpac and re-deploy? This seems like a hole in the bucket. In order to change the database option, say using ALTER DATABASE CURRENT, the database needs to be, well, writable. On-prem it’s a little simpler of course, but in Azure SQL Database, you can’t USE [master];... – Aaron Bertrand Apr 4 at 22:12
3

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

  • Wow great I will try this! – judehall Apr 5 at 21:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.