backupset table info

My employer wants me to extract information for each nightly database backup
s
for each production server.
So I have created a DTS package that extracts this info into excel using the
following script:
use msdb
go
select database_name as dbname,
convert(char(20),backup_start_date,100) as start_time,
convert(char(20),backup_finish_date,100)
as end_time, backup_size from
backupset
where backup_start_date >
(SELECT MAX(backup_start_date) FROM backupset) - 1
order by database_name, backup_start_date
Which gives the following output:
dbname start_time end_time backup_size
db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
db2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
He now wants two additional columns in the excel sheet. One with yesterday's
backup_size and 1 w ago's backup size.
Can anyone tell me how to modify this sql script to be able to retieve this
additional info from the backupset table?
Thanks,
HillaireLook at sysbackuphistory system table.
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:C0231A91-23B4-43E6-B6AD-37EDA6EC4D1A@.microsoft.com...
> My employer wants me to extract information for each nightly database
backups
> for each production server.
> So I have created a DTS package that extracts this info into excel using
the
> following script:
> use msdb
> go
> select database_name as dbname,
> convert(char(20),backup_start_date,100) as start_time,
> convert(char(20),backup_finish_date,100)
as end_time, backup_size from
> backupset
> where backup_start_date >
> (SELECT MAX(backup_start_date) FROM backupset) - 1
> order by database_name, backup_start_date
> Which gives the following output:
> dbname start_time end_time backup_size
> db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
> DB2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
> db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
> db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
> He now wants two additional columns in the excel sheet. One with
yesterday's
> backup_size and 1 w ago's backup size.
> Can anyone tell me how to modify this sql script to be able to retieve
this
> additional info from the backupset table?
> Thanks,
> Hillaire
>|||Try,
select
a.database_name as dbname,
convert(char(20),a.backup_start_date,100) as start_time,
convert(char(20),a.backup_finish_date,100) as end_time,
a.backup_size,
b.backup_size
from
msdb.dbo.backupset as a
left join
msdb.dbo.backupset as b
on
a.database_name = b.database_name
and a.backup_start_date > (SELECT MAX(backup_start_date) FROM
msdb.dbo.backupset) - 1
and b.backup_start_date = dateadd(w, -1, (SELECT MAX(backup_start_date)
FROM msdb.dbo.backupset) - 1)
order by
a.database_name,
a.backup_start_date
AMB
"gracie" wrote:

> My employer wants me to extract information for each nightly database back
ups
> for each production server.
> So I have created a DTS package that extracts this info into excel using t
he
> following script:
> use msdb
> go
> select database_name as dbname,
> convert(char(20),backup_start_date,100) as start_time,
> convert(char(20),backup_finish_date,100)
as end_time, backup_size from
> backupset
> where backup_start_date >
> (SELECT MAX(backup_start_date) FROM backupset) - 1
> order by database_name, backup_start_date
> Which gives the following output:
> dbname start_time end_time backup_size
> db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
> db2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
> db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
> db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
> He now wants two additional columns in the excel sheet. One with yesterday
's
> backup_size and 1 w ago's backup size.
> Can anyone tell me how to modify this sql script to be able to retieve thi
s
> additional info from the backupset table?
> Thanks,
> Hillaire
>|||What I actually need to accomplish is a comparison on the same excel sheet
showing yesterday's backup size and 1 w ago's backup size, side by side s
o
that he can look for any large decrepancies in the sizes.
"Uri Dimant" wrote:

> Look at sysbackuphistory system table.
>
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:C0231A91-23B4-43E6-B6AD-37EDA6EC4D1A@.microsoft.com...
> backups
> the
> yesterday's
> this
>
>