Today’s dbatools command: Measure-DbaBackupThroughput
This one’s a smaller command, but that doesn’t mean it’s without use. If you need information on backup speeds (throughput) or early warning of possible hardware issues, such as in a RAID 5 setup, this can help you.
Measure-DbaBackupThroughput ` -SqlInstance PRECISION-M7520\SQL2016 ` | Select-Object ` SqlInstance, Database, AvgThroughput, ` AvgSize, AvgDuration, MinThroughput, ` MaxThroughput, MinBackupDate, ` MaxBackupDate, BackupCount ` | Out-Gridview
Basic command usage, then we pipe it through a select statement and into a gridview. Selecting only those specific columns to cut down on redundant information, and also to get it all in one picture easier. Only measures full backups by default.
Measure-DbaBackupThroughput ` -SqlInstance PRECISION-M7520\SQL2016 ` -Database test ` -Last ` | Select-Object ` SqlInstance, Database, AvgThroughput, ` AvgSize, AvgDuration, MinThroughput, ` MaxThroughput, MinBackupDate, ` MaxBackupDate, BackupCount ` | Out-Gridview
Maybe we don’t want all the backup history for every database. Maybe we just want the most recent history for one database. The -Database parameter lets us specify which databases to check, and the -Last parameter returns only the most recent backup of each type.
Measure-DbaBackupThroughput ` -SqlInstance PRECISION-M7520\SQL2016 ` -Since (Get-Date).AddDays(-28) ` | Select-Object ` SqlInstance, Database, AvgThroughput, ` AvgSize, AvgDuration, MinThroughput, ` MaxThroughput, MinBackupDate, ` MaxBackupDate, BackupCount ` | Out-Gridview
The -Since parameter combined with Get-Date lets us get all the information since a certain point in time relative to now. Presumably this can also be done with a hard-coded date, but that seems like an edge-case scenario to me.
Measure-DbaBackupThroughput ` -SqlInstance PRECISION-M7520\SQL2016 ` -Database test ` -Type Differential ` | Select-Object ` SqlInstance, Database, AvgThroughput, ` AvgSize, AvgDuration, MinThroughput, ` MaxThroughput, MinBackupDate, ` MaxBackupDate, BackupCount ` | Out-Gridview
Use the -Type parameter to get information on different kinds of backups, accepts “Full”, “Log” or “Differential”.
Note: -Type parameter does not accept multiple values with commas, probably because the results would then have to have up to 3 times as many rows, one for each backup type.
Measure-DbaBackupThroughput ` -SqlInstance PRECISION-M7520\SQL2016 ` -ExcludeDatabase msdb, master, model, tempdb ` | Select-Object ` SqlInstance, Database, AvgThroughput, ` AvgSize, AvgDuration, MinThroughput, ` MaxThroughput, MinBackupDate, ` MaxBackupDate, BackupCount ` | Out-Gridview
If we don’t want specific databases, we can use -ExcludeDatabase for that. Useful for only checking the user databases. You don’t have to specifically exclude tempdb, but i did it anyways, just for fun.
Speaking of just for fun, i also took backups of the test database onto 3 different drives, C and two external drives. The results are somewhat less useful than i’d like, though:
Speaking of external drives, go follow me (b|t) on twitter, where i mostly talk about two things:
Previous Dbatools posts:
Backup-DbaDatabase
Restore-DbaDatabase
Get-DbaLastBackup
Get-DbaAgentJobHistory
Future Dbatools posts:
None yet!