Striped backups can be a challenge when it comes to working with SQL Clone, a product from Redgate. In a recent customer scenario, they needed to automatically build an image from the latest backup files, but there were a few caveats. The process couldn’t access the production environment, so reading msdb.backupmediafamily for file data was not an option. Additionally, the number of stripes in the backup was unknown, and the naming pattern was not consistent. To tackle this problem, PowerShell was the preferred solution. This article will explore how to take a list of files and a database name and determine the files in the latest backup.

Getting Started

The first step in this process is to create a backup. For demonstration purposes, we will use the Northwind database. By backing up this small database, we can ensure that there are a variety of files in the backup folder. Here is an example of the backup command:

BACKUP DATABASE Northwind
TO
    DISK = N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup\Northwind_20190917a.bak',
    DISK = N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup\Northwind_20190917b.bak'
    DISK = N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup\Northwind_20190917c.bak'
WITH
    NOFORMAT,
    NOINIT,
    NAME = N'Northwind-Full Database Backup',
    SKIP,
    NOREWIND,
    NOUNLOAD,
    STATS = 10;
GO

Next, we need to examine the backup files to determine the file information. Opening the files in a hex editor can provide some insights, such as the source machine and the physical file names for the database. However, finding information about the other files in the stripe can be challenging. In this case, seeking help from the SQL Server community, such as the #sqlhelp on Twitter, can be beneficial.

Getting the Files

After seeking assistance, we can start working on getting the list of files. In this scenario, we have a list of files in a folder, so we need to iterate through that list. PowerShell’s Get-ChildItem cmdlet is useful for this task. To simplify the process, we can limit the script to backup files by specifying the file extension. Here is an example:

$BackupPath = 'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup'
$BackupFiles = Get-ChildItem -Path $BackupPath -File -Include *.bak -Recurse
$FilesToRestore = @()
foreach ($File in $BackupFiles) {
    $FilesToRestore += "$($BackupPath)\$($File.Name)"
}

For striped backups, where performance is the primary concern, we can wrap this code in a foreach loop to iterate through a list of backup locations and add files from all of them to the $FilesToRestore array.

Assembling a Backup Set

Once we have the list of files, the next step is to group them together as a backup set. Fortunately, SQL Server can do this for us. We just need an instance that can read the backup file, not necessarily the one that created the backup. To simplify this process, we can use the dbatools module, a set of PowerShell cmdlets for SQL Server. Here is an example of how to assemble a backup set:

$TemporaryServerMachine = 'Plato' 
$TemporaryServerInstance = 'SQL2017'

# Install dbatools if not already installed
Install-Module dbatools

# Update dbatools if necessary
Update-Module dbatools

$FileData = Get-DbaBackupInformation -SqlInstance $TemporaryServerMachine\$TemporaryServerInstance -DatabaseName $DatabaseName -Path $FilesToRestore -WarningAction SilentlyContinue

After running this code, we can obtain a list of backup sets. To get the latest backup set, we can order the sets by the end time and select the first row. Here is an example:

$LatestBackup = $FileData | Sort-Object End -Descending | Select-Object -First 1

Finally, to get the actual files that make up the backup set, we can use a loop to iterate through the PATH property of the set. Here is an example:

$RestoreFiles = @()
foreach ($BackupFile in $LatestBackup.Path) {
    $RestoreFiles += $BackupFile
}

Now, the variable $RestoreFiles contains all the files needed to execute a restore command on another system. This list of files can be passed to any restore process, such as the Redgate SQL Clone product.

Summary

Working with striped backups in SQL Server can be challenging, but with the right approach, it is possible to automate the process of building an image from the latest backup files. By using PowerShell and the dbatools module, we can retrieve the list of files, assemble them into a backup set, and obtain the necessary files for a restore. Whether you are striping backups for performance or for disaster recovery purposes, this solution can help you streamline the process.