I had an interesting challenge recently with a customer. They were looking to work with the SQL Clone product from Redgate, but had to deal with striped backups. This isn't a problem for the product, but this customer wanted to automatically build an image from the latest backup files. While this doesn't seem hard, I had a few caveats.
- The process could not access production, meaning I couldn't read msdb.backupmediafamily for the file data
- I'm not doing a direct restore, so I just need a list of files
- I do know the database name, so I can use that.
- I don't know how many stripes there are in the backup
- I can't count on naming to be a known pattern
- This has to work from PowerShell.
I guess this could work from T-SQL and I'd get the results from PoSh, but a complete PoSh solution is cleaner.
This article will look at how to take a list of files and a database name and find out what flies are in the latest backup. I can actually get all the files, but I'll leave that to the reader. Or leave a comment asking how to do this if you can't figure it out.
Getting Started
I wasn't sure how to start this process. That's not quite true. I did know how to start, but was fumbling a bit as I wasn't sure what would work. My first step was to make a backup. I decided to use the Northwind database as it's small, and I hadn't used it in awhile. This ensured I'd have a variety of files in my backup folder.
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;
GOI then started opening up these files in a hex editor to look for file information. I found some things, like the source machine, the physical file names for the database, and more, but no hint of the other files in my stripe.
To be fair, I ran out of patience quickly here. I turned to #sqlhelp on Twitter and asked a question. I got a number of responses, but the two best ones were from Brent Ozar and Anthony Notencino. I filed Brent's post away as I wanted a T-SQL solution. If I needed it, however, I knew I could read through Brent's and perhaps get ideas on how to code this in PoSh.
Anthony's looked more like what I needed, so I sent this on to someone to look at until I could experiment. A few days later, I got my change.
Getting the Files
When I read through Anthony's post, I realized this was the template for what I wanted, but this needed some work. His first step is to get a list of files from Azure. In my case, I have a list of files in a folder, so I need to walk through that list. This should be actually easier, so I decided to build this part first. Get-ChildItem is your friend here, or at least, it was mine.
To keep things simple, and general, I looked through the docs and decided to ensure I limit my script to backup files. This will simplify the process. I also limited this to a folder, which I set in a variable. This was the first cut as this script, and it worked well, though I needed the -Recurse option added. With this list of items in the object, I add each file to an array.
$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)"
}
Keep in mind for striped backups, the primary use case is performance, which means multiple locations. In this case, we could wrap this in a foreach loop that goes through a list of backup locations and then add files from all of them to the $FilesToRestore array.
Once we have the list of files, we can then work on determining what backupsets are in the list of files.
Assembling a Backup Set
The next part of this is to actually group these files together. Fortunately, SQL Server will actually do this for us. We just need an instance that can read the backup file, not one that created the backup. To facilitate this, I added some variables for the instance I can use. Note, this was not the instance or version that created the backup.
$TemporaryServerMachine = 'Plato' $TemporaryServerInstance = 'SQL2017'
I then use dbatools to make this easier. Dbatools is a set of cmdlets that bring common functionality to PowerShell and SQL Server. This is often functionality that exists in SMO or PowerShell, but it is cumbersome or complex to implement. To get this working, this command is needed to get dbatools working. This might require some PoSh changes, so you can read the install doc.
Install-Module dbatools
You might also need to update if you haven't in some time.
Update-Module dbatools
The way to assemble a backup set is to use the Get-DbaBackupInformation cmdlet to do this. We pass in an instance to use, a database name, and the files. Then SQL Server returns a list of backup sets. This is exactly what I want to do, so I decided to use this to get the backup set. Note that my list of files might contain multiple backups. In fact, in the setup, I ran the demo backup code above and got multiple sets (I did change file names). You can see these in the immage below. I have a series of different backup files that represent a few different backups at different times.
My requirement is to get the latest set, but first, let's get a list of sets. I can use the Get-DbaBackupInformation cmdlet, and I'll assign the results to a variable. In this case, I tell it which SQL instance to use to assemble and group the files, the database I care about, and the files. I had some minor warnings pop up and suppressed them as they aren't important. In my case, I had some TDE databases backed up and these files can't be read as the certificate was on another server.
$FileData = Get-DbaBackupInformation -SqlInstance $SQLServerAdminInstance -DatabaseName $DatabaseToBeImaged -Path $FilesToRestore -WarningAction SilentlyContinue
Once this runs, I can get a list of the backup sets. In my case, I see this:
I now need to get the latest backup, which should be the latest end time. For a process that likely runs backups fairly infrequently (once a day or less), I can order by the end time. I just need one row, so the Sort-Object, Select-Object works for me.
$LatestBackup = $FileData | Sort-Object End -Descending | Select-Object -First 1
Now the last part of this is to get the actual files that make up this backup set. Fortunately the set has a PATH property that contains the files inside the set. I can then use this short set of code to actually get the list of files. I'll do this with a short loop.
$RestoreFiles = @()
foreach ( $BackupFile in $LatestBackup.Path) {
$RestoreFiles += $BackupFile
}That's it. This variable, $RestoreFiles, contains all the files I need to pass into a restore command to execute the restore on another system. In my case, I'm passing these to the Redgate SQL Clone product for a restore to create an image, but these can easily be passed to any restore process.
Summary
Getting a list of the striped backup files is fairly straightforward, but not quite as simple as you might expect. If you are striping for performance and then dumping the files into a common area for DR or admin purposes, this will work fine. If you need to get the latest list from a series of locations, you'll need to alter the script to get the combined list of files from all locations. Once you do that, this should work fine.
References
These are the sources I used to help me get this working.
- Restoring Backups from Azure Blob with dbatools - http://www.centinosystems.com/blog/sql/restoring-backups-from-azure-blob-with-dbatools/
- dbatools Get-DbaBackupInformation - https://docs.dbatools.io/#Get-DbaBackupInformation
- sp_DatabaseRestore: Open Source Database Restore Stored Procedure - https://www.brentozar.com/archive/2017/03/databaserestore-open-source-database-restore-stored-procedure/
