[sql server]查询备份文件大小

SQL SEVER中，会记录每个备份文件的详细信息在msdb.dbo.backupset ,包括大小，压缩后大小，处理时间等

要通过 msdb.dbo.backupmediafamily 用文件名找到media_set_id

Select Top 20 Convert(Varchar(40), S.database_name) AS [Database Name]
,Convert(varchar(19),S.backup_start_date, 120) AS [Start Date]
,Convert(varchar(19), S.backup_finish_date, 120) AS [Finish Date]
,Datediff(s, S.backup_start_date, S.backup_finish_date) AS [Duration (s)]
,Case
type
When 'D' Then 'FULL'
When 'L' Then 'Log'
When 'I' Then 'Differential'
When 'F' Then 'File Group' End As Type
,Convert(Varchar(15), Convert(Decimal(5, 2), Round(S.backup_size / Square(1024), 2))) AS [Size (MB)]
,Convert(Varchar(15), Convert(Decimal(5, 2), Round(S.compressed_backup_size / Square(1024), 2))) AS [CSize (MB)]
,Convert(varchar(19), S.expiration_date, 80) AS [Expiration Date]
,M.physical_device_name AS [Phys. Device Name]
,M.logical_device_name As [Logi. Device Name]
,S.backup_size ,*
From
msdb.dbo.backupset S Inner Join
msdb.dbo.backupmediafamily M ON S.media_set_id =M.media_set_id
Order By
S.backup_finish_date Desc

 

写成一个函数来查备份文件大小就方便了

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DBA].[fn_get_backup_filesize]') AND type in (N'f', N'fn'))
DROP function [DBA].[fn_get_backup_filesize]
GO

create function [DBA].[fn_get_backup_filesize](
@filename nvarchar(1000)
)
returns numeric(20,0)
as
begin
Declare @filesize  numeric(20,0) ;
declare @media_set_id int;

select @media_set_id = media_set_id from msdb.dbo.backupmediafamily
where physical_device_name = @filename;

Select @filesize = sum(compressed_backup_size)
From msdb.dbo.backupset
where media_set_id = @media_set_id;

if @filesize is null
 set @filesize = 0;
 
return @filesize;
end
go