DSC Pull Server data loss reproduction

DscAppPoolRecyclingIssue.ps1

$pullServer = Get-LabVM -Role DSCPullServer
$sqlServer = Get-LabVM -Role SQLServer2016

$before = Invoke-LabCommand -ActivityName 'Get Row Count' -ComputerName $sqlServer -ScriptBlock {
    Invoke-Sqlcmd -Database DSC -Query "SELECT COUNT(*) AS Count FROM StatusReport"
} -PassThru

Invoke-LabCommand -ActivityName 'Backup DSC Database' -ComputerName $sqlServer -ScriptBlock {
    mkdir C:\SQLBackups -Force
    $backupCmd = "BACKUP DATABASE [DSC] TO  DISK = N'C:\SQLBackups\DSC {0:yyMMdd-hhmmss}.bak' WITH NOFORMAT, NOINIT,  NAME = N'DSC-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10" -f (Get-Date)
    Invoke-Sqlcmd -Database DSC -Query $backupCmd
}

Write-Host 'Restarting Pull Server...' -NoNewline
Restart-LabVM -ComputerName (Get-LabVM -Role DSCPullServer) -Wait
Write-Host 'finished'

Write-Host 'Restarting Nodes...' -NoNewline
Restart-LabVM -ComputerName (Get-LabVM | Where-Object Name -like *node*) -Wait
Write-Host 'finished'

Start-Sleep -Seconds 60

Invoke-LabCommand -ActivityName 'Restart DSC App Pool' -ComputerName $pullServer -ScriptBlock {
    & $env:windir\system32\inetsrv\appcmd recycle apppool PSWS
}

Start-Sleep -Seconds 15 #allow the DSC Pull Server to delete the records

$after = Invoke-LabCommand -ActivityName 'Get Row Count' -ComputerName $sqlServer -ScriptBlock {
    Invoke-Sqlcmd -Database DSC -Query "SELECT COUNT(*) AS Count FROM StatusReport"
} -PassThru

Write-Host "Record count before recycling: $($before.Count)"
Write-Host "Record count after recycling: $($after.Count)"

if ($after.Count -lt $before.Count)
{
    Write-Warning "Restoring DSC database due to record loss"
    Invoke-LabCommand -ActivityName 'Restore DSC Database' -ComputerName $sqlServer -ScriptBlock {
        $restoreFile = dir C:\SQLBackups | Select-Object -Last 1
        $restoreCmd = @"
USE [master]
ALTER DATABASE [DSC] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
BACKUP LOG [DSC] TO  DISK = N'{0}' WITH NOFORMAT, NOINIT,  NAME = N'DSC 180108-030021', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5
RESTORE DATABASE [DSC] FROM  DISK = N'{0}' WITH  FILE = 1,  NOUNLOAD,  STATS = 5
ALTER DATABASE [DSC] SET MULTI_USER

GO
"@ -f $restoreFile.FullName

        Invoke-Sqlcmd -Database DSC -Query $restoreCmd

        Invoke-Sqlcmd -Database DSC -Query "SELECT COUNT(*) AS Count FROM StatusReport"
    } -PassThru
}