May 19, 2005 at 1:12 pm
I have written this script to find the backup sizes for the most recent backups but it is giving me duplicate values. Before messing with it further I wanted to check if anyone has heard of any existing scripts or utilities to find the size of recent DB and Log backups. Thanks!
SET NOCOUNT ON
use msdb
go
select "DB_name" = convert(char(20), BS.database_name),
BS.type,
convert(char(20),BS.backup_finish_date,100) as end_time,
BS.backup_size AS "backup_size in bytes",
BF.file_size
from backupset BS(nolock),
backupfile BF(nolock)
where backup_start_date > (SELECT MAX(backup_start_date) FROM backupset) - 1
AND BS.backup_set_id = BF.backup_set_id
order by database_name, backup_start_date
May 20, 2005 at 2:06 am
Try something like this:
use msdb
go
select top 200 b.backup_set_id, b.backup_start_date, b.backup_finish_date,
bm.physical_device_name,
cast (((b.backup_size / 1024) /1024) / 1024 as decimal(16,2)) AS GB
from backupset b join backupmediafamily bm
on b.media_set_id = bm.media_set_id
where b.type='d' and b.database_name = 'YourDBName'
--and bm.physical_device_name = '\\.\tape0'
order by b.backup_start_date desc
Regards
Alex
May 20, 2005 at 6:39 am
Thanks for your reply. I combined my script with yours to get the script below. Selecting backup_set_size I can now see that there are definitely duplicates. Any idea why? Thanks again...
SET NOCOUNT ON
use msdb
go
select top 50 BS.backup_set_id,
"DB_name" = convert(char(20), BS.database_name),
BS.type,
convert(char(20),BS.backup_finish_date,100) as end_time,
cast (((BS.backup_size / 1024) /1024) as decimal(16,2)) AS MB
from backupset BS(nolock) JOIN backupfile BF(nolock)
on BS.backup_set_id = BF.backup_set_id
where backup_start_date > (SELECT MAX(backup_start_date) FROM backupset) - 1
order by database_name, backup_start_date
May 20, 2005 at 12:06 pm
Its caused by your join to the backupfile table, which is sending you 1 row for every database file (mdf) and 1 row for every transaction log file (ldf), instead of backup files. Try just removing the join and see if you get what you're looking for.
Steve
May 20, 2005 at 12:13 pm
Thanks, that worked! I was using the Backupfile table earlier but I no longer need it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply