Get Database Backup Details with PowerShell

Throughout my career, I've needed to restore a SQL Server database from a backup file, and either the backup is from a system other than the server where the restored database needs to be placed, or it's from an unknown (but trusted) source. In any case, I need to know the filenames, the logical names, the file locations, the file sizes, etc., from the backup before I can do the restore. I definitely need to know how much space I need to allocate before doing the restore.

Related: Create Alert-Based Log Backups with PowerShell

In T-SQL, it's fairly straightforward to get the details of the contents of the backup file using the FILELISTONLY option of the RESTORE command.

RESTORE FILELISTONLY FROM DISK = 'C:MSSQLMSSQL11.MSSQLSERVERMSSQLBackupTicketSalesDB.bak' WITH FILE = 1GO

This brings back the details in a results grid (in SQL Server Management Studio):

That works, but if I want to use that information in different ways, it can be a little complicated. PowerShell, with a little help from SQL Server Management Objects (SMO), gives you a more versatile way to grab that data. As always, you need to connect to a SQL Server using the SMO Server object, then you'll create a Restore object.

$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') WS12SQL$rs = new-object('Microsoft.SqlServer.Management.Smo.Restore')

Next, you'll define a Backup Device Item pointing to the backup file, and add that to the Devices collection of the Restore object.

$bckfile = 'C:MSSQLMSSQL11.MSSQLSERVERMSSQLBackupTicketSalesDB.bak'$bdi = new-object ('Microsoft.SqlServer.Management.Smo.BackupDeviceItem') ($bckfile, 'File')$rs.Devices.Add($bdi)

SMO has the information it needs now and you'll use the ReadFileList() method of the Restore object to pull back the details from the backup file. You can use the Get-Member cmdlet to see the methods and properties available to you from the file results, which are returned to you as an ADO.NET DataTable object.

$fl = $rs.ReadFileList($srv)

You can then use the pipeline to pull just the properties you need, and format that in a table form.

$fl | select LogicalName, Type, Size, PhysicalName | Format-Table -AutoSize

It gets even better when we you hashtables to do a little manipulation with the results. For example, I prefer seeing size information in megabytes or gigabytes rather than bytes. I replace the Size property with a hashtable that looks like this.

@{Name='SizeMB';Expression={$_.Size / 1MB}}

This returns the individual file sizes in megabytes. Similarly, I like to break up the PhysicalName property into Drive, Folder and File components. This is easily done with these hashtables.

@{Name='Drive';Expression={Split-Path $_.PhysicalName -Qualifier}}@{Name='Folder';Expression={Split-Path $_.PhysicalName -Parent}}@{Name='File';Expression={Split-Path $_.PhysicalName -Leaf}}

So, after these changes, the command from above now looks like this.

$fl | select LogicalName, Type, @{Name='SizeMB';Expression={$_.Size / 1MB}},@{Name='Drive';Expression={Split-Path $_.PhysicalName -Qualifier}},@{Name='Folder';Expression={Split-Path $_.PhysicalName -Parent}}, @{Name='File';Expression={Split-Path $_.PhysicalName -Leaf}} | Format-Table -AutoSize

And the results are a lot more consumable.

Adding a WMI call to the Win32_LogicalDisk namespace, you can quickly gather the disk drives on the current system, the size (in GB) and free space (also in GB), to see that you can safely restore the database on the server.

$dsk = Get-WMIObject -Query 'select * from Win32_LogicalDisk where DriveType = 3'$dsk | select DeviceID, @{Name='SizeGB';Expression={$_.Size / 1GB}},@{Name='FreeGB';Expression={$_.FreeSpace / 1GB }} | Format-Table -AutoSize

Using these tools you can do some quick ad hoc analysis before you restore, or you can use these snippets to build a script that automates the whole process.

Related: PowerShell the SQL Server Way