Disabling Indices in a -bacpac file (Sql Server)

We store most of our application’s data in an SQL server database. Sometimes, we need to transfer the data locally to debug defects related to production data. The database grows daily, and restoring the database locally takes more and more time. So we had the idea to disable indices because they are not needed for debugging locally, but restoring them takes quite a lot of time. We can’t disable indices before exporting the data to a bacpac file because it’s the productive database, so we looked into a way to disable them within the bacpac file. Of course, I used a F# script😊.

The script looks like this:

open System.IO
open System.IO.Compression
open System.Text
open System.Text.Json
open System.Xml
open System.Xml.Linq

let getIndexElements exclusions (model: XDocument) =
    model.Descendants()
    |> Seq.filter (fun element ->
        match element.Name.LocalName, element.Attribute(XName.Get("Type")), element.Attribute(XName.Get("Name")) with
        | "Element", attr, name -> attr.Value = "SqlIndex" && (exclusions |> List.contains name.Value |> not)
        | _ -> false
    )

let disableIndex (indexElement:  XElement) =
    let disableElement = XElement(XName.Get("Property", "http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02"))
    let isDisabledAttr = XAttribute("Name", "IsDisabled")
    let valueAttr = XAttribute("Value", "True")
    disableElement.Add(isDisabledAttr)
    disableElement.Add(valueAttr)
    indexElement.Add(disableElement)

let run sourcePath (exclusions: string) =
    let exclusions = exclusions.Split(';') |> Seq.map (fun s -> s.Trim()) |> Seq.toList

    printfn "Opening archive..."
    use archive = ZipFile.Open(sourcePath, ZipArchiveMode.Update)

    printfn "Extracting model..."
    let modelEntry = archive.GetEntry("model.xml")
    let modelStream = modelEntry.Open()
    let model = modelStream |> XDocument.Load
    modelStream.Dispose()

    printfn "Disabling indexes..."
    model
    |> getIndexElements exclusions
    |> Seq.iter disableIndex

    printfn "Updating model..."
    modelEntry.Delete ()
    let newModel = archive.CreateEntry("model.xml")
    use stream = newModel.Open()
    model.Save stream

    printfn "Updating hash..."
    let originEntry = archive.GetEntry("Origin.xml")
    let originStream = originEntry.Open()
    let origin = originStream |> XDocument.Load
    originStream.Dispose()
    let hashElement = origin.Descendants(XName.Get("Checksum", "http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02")) |> Seq.head

    use sha = System.Security.Cryptography.SHA256.Create()

    stream.Position <- 0L
    let hash = stream |> sha.ComputeHash
    let sb = StringBuilder(hash.Length * 2)
    hash |> Array.iter (fun b -> sb.Append(b.ToString("x2")) |> ignore)
    hashElement.Value <- sb.ToString().ToUpperInvariant()
    originEntry.Delete()
    let newOriginEntry = archive.CreateEntry("Origin.xml")
    use stream = newOriginEntry.Open()
    origin.Save stream

    printfn "finishing up..."
    archive.Dispose()

    printfn "done - enjoy a fast(er) restore"


run @"D:\DatabaseTrials\Main-2025-05-06-23-03.bacpac" "[dbo].[VersionInfo].[UC_Version]"

It’s not the nicest code, but it works, and I blame the mutability of the data types used for it. 😅

We can call the run function with two arguments. The first is the path to the bacpac file, the second is a ;-separated list of exclusions (<schema>.<table name>.<index name>).

The algorithm has the following steps:

1. Open the bacpac (it’s a zip).
2. Open the model.xml file.
3. Find all indices we want to disable (all XML elements with Name = Element, Attribute Type = SqlIndex, Attribute Name not in exclusions).
4. Add an XML node <Property Name=”IsDisabled” Value=”True”> to these indices nodes.
5. Update the changed model.xml in the archive.
6. Update the hash (SHA265) of the model.xml inside origin.xml (otherwise the file is considered to be corrupt on import)
7. Save the archive

Afterwards, one can import the bacpac using SQL Server Management Studio, for example.