Sitecore – Removing IAR items from the database

With the introduction of IAR (Items as Resource), the horrible task of upgrading the items in the database came to and end. For upgrades, this means we can remove the items from the database, as they are now stored in the IAR files. The Sitecore UpdateApp is able to remove all items from the database that are contained in the IAR file if they aren’t modified by the user.

There’s a chance that sometimes you just want to remove all items that are in the IAR file, even if they are modified. Unfortunately however, there’s no built in way to do this with the Sitecore UpdateApp or the Sitecore CLI yet. Luckily for us, all we have to do is to read the items from the IAR file, remove them from the database, and run the cleanup tool.

I’ve written a Sitecore Powershell script for this, that you can run. Place the IAR files for which you want to delete the items in App_Data\ItemsToRemove, and keep the normal folder structure for the databases. For example, when you have an IAR file for the master database, place it in App_Data\ItemsToRemove\master.

You can run the script below in your Sitecore Powershell ISE. The code could be prettier, however I’m more skilled in writing C# than Powershell 😉
Please note: backup your database before running, and use at your own risk.

Sitecore 10.2 method

function ClearCaches()
{
    Write-Host "Clearing caches"
    Get-Cache | % { $_.Clear() }
    Write-Host "Clearing caches done"
}

function RemoveItems($databaseName)
{
    $resourceLoaderType = ([System.Type]::GetType("Sitecore.Data.DataProviders.ReadOnly.Protobuf.IResourceLoader, Sitecore.Data.ResourceItems.ProtobufNet"))
    $resourceLoader = [Sitecore.DependencyInjection.ServiceLocator]::ServiceProvider.GetService($resourceLoaderType)
    $path = [Sitecore.MainUtil]::MapPath("/App_Data/ItemsToRemove/$databaseName/")
    $paths = [System.Collections.Generic.List[String]]@($path)
    $defaultFieldValues = New-Object -TypeName 'System.Collections.Generic.Dictionary[[guid], [string]]'
    $itemDataSet = $resourceLoader.LoadFromFiles($paths, "dat", $defaultFieldValues)
    $database = [Sitecore.Configuration.Factory]::GetDatabase($databaseName)
    $connectionString = [System.Configuration.ConfigurationManager]::ConnectionStrings[$databaseName]
    $callContext = New-Object -TypeName "Sitecore.Data.DataProviders.CallContext, Sitecore.Kernel" -ArgumentList @($database.DataManager, $database.DataProviders.Count)
    $sqlDataProvider = New-Object -TypeName "Sitecore.Data.SqlServer.SqlServerDataProvider, Sitecore.Kernel" -ArgumentList @($connectionString)
    $itemsRemoved = $false

    Write-Host "Removing items for database $databaseName"
    Write-Host "Using the files in $path for database $databaseName"
    New-UsingBlock (New-Object Sitecore.Data.BulkUpdateContext) {
        foreach($itemRecord in $itemDataSet.Definitions.Values)
        {
            $id = New-Object -TypeName "Sitecore.Data.ID, Sitecore.Kernel" -ArgumentList @($itemRecord.ID)
            $itemDefinition = $sqlDataProvider.GetItemDefinition($id, $callContext)
            if($itemDefinition)
            {
                Write-Host "Removing item $($itemRecord.ID) from $database"
                $null = $sqlDataProvider.DeleteItem($itemDefinition, $callContext)
                $itemsRemoved = $true
            }
        }
    }
    Write-Host "Removing items for database $databaseName done"
    if($itemsRemoved)
    {
        Write-Host "Cleaning database $databaseName"
        $null = $database.CleanupDatabase()
        Write-Host "Cleaning database $databaseName complete"
    }
}

ClearCaches
RemoveItems "master"
RemoveItems "core"
RemoveItems "web"
ClearCaches
Write-Host "Removing items finished"

Sitecore 10.1 method

function ClearCaches()
{
    Write-Host "Clearing caches"
    Get-Cache | % { $_.Clear() }
    Write-Host "Clearing caches done"
}

function RemoveItems($databaseName)
{
    $baseLogType = ([System.Type]::GetType("Sitecore.Abstractions.BaseLog, Sitecore.Kernel"))
    $baseLog = [Sitecore.DependencyInjection.ServiceLocator]::ServiceProvider.GetService($baseLogType)
    $path = [Sitecore.MainUtil]::MapPath("/App_Data/ItemsToRemove/$databaseName/")
    $paths = [System.Collections.Generic.List[String]]@($path)
    $dataProvider = New-Object -TypeName "Sitecore.Data.DataProviders.ReadOnly.Protobuf.ProtobufDataProvider, Sitecore.Kernel" -ArgumentList @($paths, $baseLog)
    $itemDataSet = $dataProvider.DataSet
    $database = [Sitecore.Configuration.Factory]::GetDatabase($databaseName)
    $connectionString = [System.Configuration.ConfigurationManager]::ConnectionStrings[$databaseName]
    $callContext = New-Object -TypeName "Sitecore.Data.DataProviders.CallContext, Sitecore.Kernel" -ArgumentList @($database.DataManager, $database.DataProviders.Count)
    $sqlDataProvider = New-Object -TypeName "Sitecore.Data.SqlServer.SqlServerDataProvider, Sitecore.Kernel" -ArgumentList @($connectionString)
    $itemsRemoved = $false

    Write-Host "Removing items for database $databaseName"
    Write-Host "Using the files in $path for database $databaseName"
    New-UsingBlock (New-Object Sitecore.Data.BulkUpdateContext) {
        foreach($itemRecord in $itemDataSet.Definitions.Values)
        {
            $id = New-Object -TypeName "Sitecore.Data.ID, Sitecore.Kernel" -ArgumentList @($itemRecord.ID)
            $itemDefinition = $sqlDataProvider.GetItemDefinition($id, $callContext)
            if($itemDefinition)
            {
                Write-Host "Removing item $($itemRecord.ID) from $database"
                $null = $sqlDataProvider.DeleteItem($itemDefinition, $callContext)
                $itemsRemoved = $true
            }
        }
    }
    Write-Host "Removing items for database $databaseName done"
    if($itemsRemoved)
    {
        Write-Host "Cleaning database $databaseName"
        $null = $database.CleanupDatabase()
        Write-Host "Cleaning database $databaseName complete"
    }
}

ClearCaches
RemoveItems "master"
RemoveItems "core"
RemoveItems "web"
ClearCaches
Write-Host "Removing items finished"

2 Replies to “Sitecore – Removing IAR items from the database”

  1. Thanks Maarten!
    It doesn’t work on Sitecore 10.1 due to a large refactor of the Protobuf code, so I just spun up a new 10.2 to run it against a 10.1 DB with a 10.1 IAR file.
    Cheers
    Mark

Leave a Reply

Your email address will not be published.