[Bug] Issue handling restores of striped backups from non file/history sources

[Stuart-Moore]

Before submitting a bug report:

• Running latest release of dbatools
• Verified errors are not related to permissions
• Is this bug with Copy-DbaDatabase? You can replicate it using Backup-DbaDatabase ... | Restore-DbaDatabase ...

From an issue reported by @sql-williamd and @SQLDBAWithABeard on slack where they were getting spurious restore errors restoring striped log files from Azure.

Turned out to be an issue with all striped restores not just azure.

Expected Behavior

Continuing a restore with striped log files should work

Actual Behavior

It didn't work

[Stuart-Moore]

reopening to allow @sql-williamd to update

[sql-williamd]

I have a repro for this issue so that Azure Blob Storage striped files can be tested.

You'll need to create a storage account and container etc for the whole thing to work, instructions are in the repro file.

If you have questions, just ping me.

Repro.txt

[Stuart-Moore]

Cool. I'll fire it up tomorrow and work through it

…

-- Stuart Moore

On 16 April 2019 at 09:42:35, William Durkin ***@***.******@***.***)) wrote: I have a repro for this issue so that Azure Blob Storage striped files can be tested. You'll need to create a storage account and container etc for the whole thing to work, instructions are in the repro file. If you have questions, just ping me. Repro.txt(https://github.com/sqlcollaborative/dbatools/files/3083875/Repro.txt) — You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub(#5357 (comment)), or mute the thread(https://github.com/notifications/unsubscribe-auth/AFWoZuTIdWjlFNe9mfevIIEEv6zSqbnOks5vhYz7gaJpZM4cpXQq).

[Stuart-Moore]

Hi william, is that repro missing some lines? I've made a guess at what was missing and come up with

##########################################################################
# PowerShell code to do the restores
# download the dbatools fix version https://github.com/sqlcollaborative/dbatools/tree/sbifix
# unzip to c:\temp\dbatools-sbifix\
#
# Change all the [] variables (5 in total)
#
# Run the following once with $restoreType = 'FULL'
# Then run it with $restoreType = 'LOG'
# Log restore runs successfully once, then fails becuase it tries to restore the second file in the 4 file log backup
#
# WARNING: [10:32:50][Restore-DbaDatabase] Failure | System.Data.SqlClient.SqlError: The media set has 4 media families but only 1 are provided. All members must be provided.
##########################################################################

Import-Module "C:\temp\dbatools-sbifix\dbatools.psd1"
Import-Module -Name Az

# Connect & Authenticate to Azure (just manual for now and only needs running once)
Connect-AzAccount  

$subscriptionID='de9d1c6f-4705-4984-be79-59e59cb7a95c'
$storageAccountName= 'azdbatools'
$containerName= 'sql'  
$resourceGroupName='dbatools'
$databaseName = 'MyDB'
$instanceName = 'localhost\sql2016'
$restoreType = 'FULL' # FULL or LOG

$DaysBack = 1
$CutOffDate =  (get-date).AddDays(-$DaysBack)
$fileList = @()

switch ($restoreType)
    {
        "FULL" {$fileType = ".bak"; break}
        "LOG"  {$fileType = '.trn'; break}
    }
$fileFilter = "*$databaseName*" + $fileType

# Set the tenant, subscription and environment for use in the rest of   
$AzContext = Set-AzContext -SubscriptionId $subscriptionID   

# Get the access keys for the Azure Resource Manager storage account  
$accountKeys = Get-AzStorageAccountKey -ResourceGroupName $resourceGroupName -Name $storageAccountName  

# Create a new storage account context using an Azure Resource Manager storage account  
$storageContext = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $accountKeys[0].Value

# jump into the container we want to scan through
$container = Get-AzStorageContainer -Container $containerName -Context $storageContext

# load the blobs from that container
$blobs = Get-AzStorageBlob -Container $container.Name -Blob $fileFilter -Context $storageContext

$filelist = @()
# build a list of URLs to the blob files for the restore command to access
if ($blobs -ne $null)
    {
        foreach ($Blob in $blobs)
        {
           if ($Blob.LastModified -ge $CutOffDate)  # Only process the recent files to keep this list shorter
            {
              $fileList += $container.CloudBlobContainer.Uri.AbsoluteUri + "/" + $blob.Name
            }
        }
    }

if ($fileList.Count -gt 0)
    {
        $fileList | Restore-DbaDatabase -SqlInstance $instanceName -DatabaseName mydb2 -NoRecovery -ReplaceDbNameInFile -WithReplace
    }

    $fileFilter = "*MyDb*trn"

    $blobs = Get-AzStorageBlob -Container $container.Name -Blob $fileFilter -Context $storageContext
    $filelist = @()

# build a list of URLs to the blob files for the restore command to access
if ($blobs -ne $null)
    {
        foreach ($Blob in $blobs)
        {
           if ($Blob.LastModified -ge $CutOffDate)  # Only process the recent files to keep this list shorter
            {
              $fileList += $container.CloudBlobContainer.Uri.AbsoluteUri + "/" + $blob.Name
            }
        }
    }

if ($fileList.Count -gt 0)
    {
        $fileList | Restore-DbaDatabase -SqlInstance $instanceName -DatabaseName mydb2 -NoRecovery -Continue -ReplaceDbNameInFile
    }

which is working as expected.
Slight issue with a warning about fullname that shouldn't be there, but it's doing the restore properly

[Stuart-Moore]

btw, this went into master with 0.9.805 so you can check with the normal module

[sql-williamd]

Sorry for the delay - this appears to be working in the latest stable/prod version of dbatools now. It can be closed as far as I am concerned.

[potatoqualitee]

SWEET! Thank you both 💯