Opening your SQL Backup Folder using Powershell

Sometimes I find remembering where a particular server sends its backups to a nightmare.

You might have servers backing up to different locations, you might have different locations for individual databases and different locations for your fulls, diffs and logs. You might be trying to get your head around a customer’s set up, where the backups make no logical sense at all.

Whatever you’re up to, at some point, for some reason you’re going to need to access your backup location to get at the files.

I used to figure out where that was by querying msdb if I was on a customer’s kit or if onsite where we use MinionBackup, hit up the minion log tables. Minion does make life slightly easier but either way it’s still a pain.

Running a query, copying a path, opening File Explorer and pasting in that path quickly becomes tedious when you have to do it numerous times.

Being someone who’s always up for making my life easier, I put together this powershell script. Just tap in your server name, database name and backup type as prompted and the script will go off, find the backup location of the latest backup fitting your parameters and open that location in File Explorer.

[reflection.assembly]::loadwithpartialname("Microsoft.SqlServer.Smo") | out-null
$ServerName = Read-Host -Prompt 'SQL Instance: '
$DatabaeName = Read-Host -Prompt 'Database Name: '
$BackupType = Read-Host -Prompt 'Backup Type (Full, Diff or Log)'
$BackupType = switch ($BackupType)
{
    'Full' {'D'}
    'Diff' {'I'}
    'Log' {'L'}
    default {'Error'}
}
if ($BackupType -eq 'Error')
{
    throw "Invalid backup type specified, please choose either Full, Diff or Log"
}

$SMO = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName;
$SMO.ConnectionContext.StatementTimeout = 0;
$BackupPath = $SMO.Databases['msdb'].ExecuteWithResults("SELECT TOP 1 backupmediafamily.physical_device_name FROM backupset JOIN backupmediafamily ON backupset.media_set_id = backupmediafamily.media_set_id WHERE backupset.database_name = '$($DatabaeName)' AND backupset.type = '$($BackupType)' ORDER BY backupset.backup_start_date DESC")
Invoke-Item $BackupPath.Tables[0].Rows[0].ItemArray[0].Substring(0, $BackupPath.Tables[0].Rows[0].ItemArray[0].LastIndexOf(''))

It’s a little thing I know, but sometimes those little things can make a difference.

I hope you find it useful