< Back
Measure-DbaBackupThroughput
Post
NAME Measure-DbaBackupThroughput
SYNOPSIS
Determines how quickly SQL Server is backing up databases to media.
SYNTAX
Measure-DbaBackupThroughput [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>
[[-SqlCredential] <Pscredential>] [[-Database] <System.Object[]>] [[-ExcludeDatabase] <System.Object[]>] [[-Since]
<Datetime>] [-Last <Switch>] [[-Type] <String>] [[-DeviceType] <System.String[]>] [-EnableException <Switch>]
[<CommonParameters>]
DESCRIPTION
Returns backup history details for one or more databases on a SQL Server.
Output looks like this:
SqlInstance : sql2016
Database : SharePoint_Config
AvgThroughput : 1.07 MB
AvgSize : 24.17
AvgDuration : 00:00:01.1000000
MinThroughput : 0.02 MB
MaxThroughput : 2.26 MB
MinBackupDate : 8/6/2015 10:22:01 PM
MaxBackupDate : 6/19/2016 12:57:45 PM
BackupCount : 10
PARAMETERS
-Database [<System.Object[]>]
The database(s) to process. Options for this list are auto-populated from the server. If unspecified, all
databases will be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DeviceType [<System.String[]>]
Specifies one or more DeviceTypes to use in filtering backup sets. Valid values are "Disk", "Permanent Disk
Device", "Tape", "Permanent Tape Device", "Pipe", "Permanent Pipe Device" and "Virtual Device", as well as
custom integers for your own DeviceTypes.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables
advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own
try/catch.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.Object[]>]
The database(s) to exclude. Options for this list are auto-populated from the server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Last [<Switch>]
If this switch is enabled, only the last backup will be measured.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Since [<Datetime>]
All backups taken on or after the point in time represented by this datetime object will be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Type [<String>]
By default, this command measures the speed of Full backups. Valid options are "Full", "Log" and
"Differential".
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Backup, Database
Author: Chrissy LeMaire (@cl), netnerds.net
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Measure-DbaBackupThroughput -SqlInstance sql2016
Parses every backup in msdb's backuphistory for stats on all databases.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Measure-DbaBackupThroughput -SqlInstance sql2016 -Database AdventureWorks2014
Parses every backup in msdb's backuphistory for stats on AdventureWorks2014.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Measure-DbaBackupThroughput -SqlInstance sql2005 -Last
Processes the last full, diff and log backups every backup for all databases on sql2005.
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Measure-DbaBackupThroughput -SqlInstance sql2005 -Last -Type Log
Processes the last log backups every backup for all databases on sql2005.
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>Measure-DbaBackupThroughput -SqlInstance sql2016 -Since (Get-Date).AddDays(-7) | Where-Object {
$_.MinThroughput.Gigabyte -gt 1 }
Gets backup calculations for the last week and filters results that have a minimum of 1GB throughput
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Measure-DbaBackupThroughput -SqlInstance sql2016 -Since (Get-Date).AddDays(-365) -Database bigoldb
Gets backup calculations, limited to the last year and only the bigoldb database
RELATED LINKS
https://dbatools.io/Measure-DbaBackupThroughput
SYNOPSIS
Determines how quickly SQL Server is backing up databases to media.
SYNTAX
Measure-DbaBackupThroughput [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>
[[-SqlCredential] <Pscredential>] [[-Database] <System.Object[]>] [[-ExcludeDatabase] <System.Object[]>] [[-Since]
<Datetime>] [-Last <Switch>] [[-Type] <String>] [[-DeviceType] <System.String[]>] [-EnableException <Switch>]
[<CommonParameters>]
DESCRIPTION
Returns backup history details for one or more databases on a SQL Server.
Output looks like this:
SqlInstance : sql2016
Database : SharePoint_Config
AvgThroughput : 1.07 MB
AvgSize : 24.17
AvgDuration : 00:00:01.1000000
MinThroughput : 0.02 MB
MaxThroughput : 2.26 MB
MinBackupDate : 8/6/2015 10:22:01 PM
MaxBackupDate : 6/19/2016 12:57:45 PM
BackupCount : 10
PARAMETERS
-Database [<System.Object[]>]
The database(s) to process. Options for this list are auto-populated from the server. If unspecified, all
databases will be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DeviceType [<System.String[]>]
Specifies one or more DeviceTypes to use in filtering backup sets. Valid values are "Disk", "Permanent Disk
Device", "Tape", "Permanent Tape Device", "Pipe", "Permanent Pipe Device" and "Virtual Device", as well as
custom integers for your own DeviceTypes.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables
advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own
try/catch.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.Object[]>]
The database(s) to exclude. Options for this list are auto-populated from the server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Last [<Switch>]
If this switch is enabled, only the last backup will be measured.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Since [<Datetime>]
All backups taken on or after the point in time represented by this datetime object will be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Type [<String>]
By default, this command measures the speed of Full backups. Valid options are "Full", "Log" and
"Differential".
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Backup, Database
Author: Chrissy LeMaire (@cl), netnerds.net
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Measure-DbaBackupThroughput -SqlInstance sql2016
Parses every backup in msdb's backuphistory for stats on all databases.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Measure-DbaBackupThroughput -SqlInstance sql2016 -Database AdventureWorks2014
Parses every backup in msdb's backuphistory for stats on AdventureWorks2014.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Measure-DbaBackupThroughput -SqlInstance sql2005 -Last
Processes the last full, diff and log backups every backup for all databases on sql2005.
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Measure-DbaBackupThroughput -SqlInstance sql2005 -Last -Type Log
Processes the last log backups every backup for all databases on sql2005.
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>Measure-DbaBackupThroughput -SqlInstance sql2016 -Since (Get-Date).AddDays(-7) | Where-Object {
$_.MinThroughput.Gigabyte -gt 1 }
Gets backup calculations for the last week and filters results that have a minimum of 1GB throughput
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Measure-DbaBackupThroughput -SqlInstance sql2016 -Since (Get-Date).AddDays(-365) -Database bigoldb
Gets backup calculations, limited to the last year and only the bigoldb database
RELATED LINKS
https://dbatools.io/Measure-DbaBackupThroughput