Wednesday, March 22, 2017

Backup file size


select p.backup_size/POWER(2.0,30.0) ,p.database_name,p.[Server] as 'DBServer', p.physical_device_name,p.last_db_backup_date from (

SELECT

A.[Server],

A.last_db_backup_date,

B.backup_start_date,

B.expiration_date,

B.backup_size,

B.logical_device_name,

B.physical_device_name,

B.backupset_name,

B.description ,

b.database_name

FROM

(

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

msdb.dbo.backupset.database_name,

MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE msdb..backupset.type = 'D'




 
GROUP BY

msdb.dbo.backupset.database_name

) AS A




 
LEFT JOIN

(

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

msdb.dbo.backupset.database_name,

msdb.dbo.backupset.backup_start_date,

msdb.dbo.backupset.backup_finish_date,

msdb.dbo.backupset.expiration_date,

msdb.dbo.backupset.backup_size,

msdb.dbo.backupmediafamily.logical_device_name,

msdb.dbo.backupmediafamily.physical_device_name,

msdb.dbo.backupset.name AS backupset_name,

msdb.dbo.backupset.description

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE msdb..backupset.type = 'D'

) AS B

ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]

and A.[last_db_backup_date] > '03/20/2017'

)

as p

where backup_size is not null

--and database_name = 'mydb' if to find size only for that db

order by database_name

Found the on sqlservercentral . Very cool demo to convert the size

* 250 Gb */
DECLARE @MY_NumberOfBytes NUMERIC(12,0) = 268435456000;
/* bytes */
SELECT
'Bytes' AS UNIT
,@MY_NumberOfBytes AS NUMBER
UNION ALL
/* Kilobytes, divide by 2^10 (1024) */
SELECT
'Kilobytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,10.0) AS NUMBER
UNION ALL
/* Megabytes, divide by 2^20 (1048576) */
SELECT
'Megabytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,20.0) AS NUMBER
UNION ALL
/* Gigabytes, divide by 2^30 (1073741824) */
SELECT
'Gigabytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,30.0) AS NUMBER
UNION ALL
/* Terabytes, divide by 2^40 (1099511627776) */
SELECT
'Terabytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,40.0) AS NUMBER;

No comments:

Post a Comment