Backup File Size

Question

Post reply

Backup File Size

Raghavender Chavva

SSCrazy Eights

Points: 8669
More actions
September 6, 2010 at 6:02 am

#229064

Hi All,
Can we estimate how much disk space will be consumed by a database backup, without doing the backup or by just looking at database size(data file and log file size)?
Thank You.
Regards,
Raghavender Chavva

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

try this :-

SELECT s.database_name,

m.physical_device_name,

cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,

CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,

s.backup_start_date,

CASE s.[type]

WHEN 'D' THEN 'Full'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Transaction Log'

END as BackupType,

s.server_name,

s.recovery_model

FROM msdb.dbo.backupset s

inner join msdb.dbo.backupmediafamily m

ON s.media_set_id = m.media_set_id

WHERE DATEPART(WEEKDAY, backup_start_date) = 2 and backup_start_date > '2010-09-02' and type = 'D'

ORDER BY database_name, backup_start_date, backup_finish_date

you can refine it further as per your requirement

----------
Ashish

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 2

ashish.kuriyal (9/6/2010)
try this :-
SELECT s.database_name,
m.physical_device_name,
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE DATEPART(WEEKDAY, backup_start_date) = 2 and backup_start_date > '2010-09-02' and type = 'D'
ORDER BY database_name, backup_start_date, backup_finish_date
you can refine it further as per your requirement

we dont have any entry of this database in the msdb.dbo.backupset and msdb.dbo.backupmediafamily tables.

Thank You.

Regards,
Raghavender Chavva

crazy4sql SSCoach Points: 19590 More actions · Answer 3

ohhok, so you have the database but it was never backed up because of thinking on the estimate size it will take? 😀 😀 😀

----------
Ashish

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 4

ashish.kuriyal (9/6/2010)
ohhok, so you have the database but it was never backed up because of thinking on the estimate size it will take? 😀 😀 😀

you can think that it was refreshed from other instance to which we dont have access.

And to estimate the SAN for backups ?

Do you have any mechanism to estimate the database backup file size without doing the backup ?

Thank You.

Regards,
Raghavender Chavva

crazy4sql SSCoach Points: 19590 More actions · Answer 5

if its not sql compressed backup or litespeed compressed backup, then for estimate, it should be nearly equaly to mdf file size of database.

----------
Ashish

homebrew01 SSC Guru Points: 55553 More actions · Answer 6

ashish.kuriyal (9/6/2010)
if its not sql compressed backup or litespeed compressed backup, then for estimate, it should be nearly equaly to mdf file size of database.

Unless you have a lot of free space in the database or log. Free space will not get backed up, so your backup could be smaller by that amount.