May 19, 2005 at 10:21 am
I am writing a script that will return the backup sizes for last night's DB and log backups. However, I am seemingly getting duplicate values back for the DBs. I also select file_size from the backupfile table and it gives a different value for each of the seemingly duplicate values. What am I missing? Also, if you already have a better script for doing this please pass it on. 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
frombackupset BS(nolock),
backupfile BF(nolock)
where backup_start_date > (SELECT MAX(backup_start_date) FROM backupset) - 1
ANDBS.backup_set_id = BF.backup_set_id
order by database_name, backup_start_date
May 19, 2005 at 11:34 am
is it because backup file has an entry for both Database and Log?
Perhaps you want to group and sum?
May 19, 2005 at 12:14 pm
Thanks for your reply.
Yes, there is an entry for both the Log and the DB and they are listed in the Type column as 'L' or 'D' respectively. The problem is that two database backups are listed for the same backup time for each database and I don't understand why there is a duplicate, or if it even is a duplicate. Thanks for any additional info you can provide...
May 20, 2005 at 6:35 am
I modified the script to select the backup_set_id so I can now see that there are definitely duplicates. Any additional feedback on this script would be greatly appreciated. Thanks!
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
frombackupset BS(nolock) JOIN backupfile BF(nolock)
onBS.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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply