Need help on how to write this report.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Need help on how to write this report.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2008-07-22 : 11:17:50

Hi,

Does anyone knows where can I get a script to generate backup and restore information
like the one show below. I need to do this quick for my boss and can't think of a way
to write this fast. Any helps would greatly appreciate.

DatabaseName Backupstartat Backuptime Restoretime AvgFinishTime
-------------------------------------------------------------------
Companydb1 8:30 PM 20 Min 150 Min 11:20 PM
Testdb2 10:15 PM 2 Min 5 Min 10:22 PM
Testdb3 9:30 PM 120 Min 140 Min 12:50 AM

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-22 : 11:19:25

Look at backupsets and Restorehistory tables in MSDB databases. You can probably join with ID.

sqldba20
Posting Yak Master

183 Posts

Posted - 2008-07-22 : 14:53:25

/* This script will display when the last database backup was taken and is stored at what location */

SELECT A.database_name as 'DBName',
A.backup_start_date as 'Backup Started',
A.backup_finish_date as 'Backup Finished',
B.physical_device_name as 'Backup Filename'
FROM msdb.dbo.backupset A,
msdb.dbo.backupmediafamily B,
(SELECT database_name,
MAX(backup_finish_date) as 'maxfinishdate'
FROM msdb.dbo.backupset
WHERE Type = 'D'
GROUP BY database_name) C
WHERE A.media_set_id = B.media_set_id AND
A.backup_finish_date = C.maxfinishdate AND
A.type = 'D'
AND A.Database_Name in (select name from master..sysdatabases)
ORDER BY backup_finish_date

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-22 : 15:09:27

Just wrote this one to give you start:

select bs.database_name,bs.backup_start_date as Backupstartat,bs.backup_finish_date as backupendtime,Datediff(mm
,bs.backup_start_date,bs.backup_finish_date)as Backuptime,datediff(mm,rh.restore_date,rh.stop_at)as Restoretime
from dbo.backupset bs inner join dbo.restorehistory rh
on bs.database_name = rh.destination_database_name

NguyenL71
Posting Yak Master

228 Posts

Posted - 2008-07-22 : 16:07:09

Thank you. it would be nice to have a script to do this kind of admin stuffs.

quote:
Originally posted by sqldba20

/* This script will display when the last database backup was taken and is stored at what location */

SELECT A.database_name as 'DBName',
A.backup_start_date as 'Backup Started',
A.backup_finish_date as 'Backup Finished',
B.physical_device_name as 'Backup Filename'
FROM msdb.dbo.backupset A,
msdb.dbo.backupmediafamily B,
(SELECT database_name,
MAX(backup_finish_date) as 'maxfinishdate'
FROM msdb.dbo.backupset
WHERE Type = 'D'
GROUP BY database_name) C
WHERE A.media_set_id = B.media_set_id AND
A.backup_finish_date = C.maxfinishdate AND
A.type = 'D'
AND A.Database_Name in (select name from master..sysdatabases)
ORDER BY backup_finish_date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources