T-SQL: Get Database Backup Throughput
Declare @dbname sysname
Declare @OrderBY int
Set @dbname = NULL --Show All
--Set @dbname = '' --Per Database
If @dbname IS NULL Begin Set @OrderBY = 1 End Else Begin Set @OrderBy = 2 End;
Select
bs.server_name as 'Server'
,UPPER(bs.database_name) AS 'Database'
,bs.recovery_model as 'Recovery Model'
,CASE bmf.device_type WHEN 2 THEN 'Disk' When 5 Then 'Tape' When 7 Then 'Virtual Device' Else Convert(varchar(5),bmf.device_type) End as 'Device Type'
--bs.media_set_id
--,bmf.family_sequence_number
,bs.backup_start_date AS 'Start Backup'
,bs.backup_finish_date AS 'Finish Backup'
,DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) as 'Duration (sec)'
,Convert(Decimal(18,3),bs.backup_size / 1048576.0) AS 'Data/Rows Size (MB)'
,Convert(Decimal(18,3),(bs.backup_size / 1048576.0) / IIF(DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) > 0, DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date), 1)) as 'MB/sec'
,bmf.physical_device_name AS 'Backup File'
From msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf
ON bs.media_set_id = bmf.media_set_id
Where
bs.type in ('D', 'I')
and bmf.family_sequence_number = 1
and (bs.database_name = @dbname or @dbname is null)
and bs.backup_finish_date > getdate() - 1
Order by
Case When @OrderBY = 1 Then bs.backup_finish_date End Desc,
Case When @OrderBY = 2 Then bs.database_name End Asc, bs.backup_finish_date Desc
|
|
|
|
|
|