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
Roy Harvey (SQL Server MVP) - 22 Jul 2008 16:36 GMT
What version of SQL Server are you running? It makes all the
difference for this. In SQL Server 2005 I get the backup information
from msdb..backupset.
Roy Harvey
Beacon Falls, CT
>Hi,
>
[quoted text clipped - 9 lines]
>Testdb2 10:15 PM 2 Min 5 Min 10:22 PM
>Testdb3 9:30 PM 120 Min 140 Min 12:50 AM
Lam Nguyen - 22 Jul 2008 18:49 GMT
Sorry, I use SQL 2000. Thanks.
> What version of SQL Server are you running? It makes all the
> difference for this. In SQL Server 2005 I get the backup information
[quoted text clipped - 16 lines]
> >Testdb2 10:15 PM 2 Min 5 Min 10:22 PM
> >Testdb3 9:30 PM 120 Min 140 Min 12:50 AM
Roy Harvey (SQL Server MVP) - 22 Jul 2008 19:06 GMT
>Sorry, I use SQL 2000. Thanks.
It turns out that in 2000 I also get the backup information from
msdb..backupset.
Roy Harvey
Beacon Falls, CT
William Vaughn (MVP) - 22 Jul 2008 16:42 GMT
I would use get Visual Studio to generate a simple report that the
ReportViewer control can display.

Signature
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
> Hi,
>
[quoted text clipped - 9 lines]
> Testdb2 10:15 PM 2 Min 5 Min 10:22 PM
> Testdb3 9:30 PM 120 Min 140 Min 12:50 AM
Bob - 23 Jul 2008 09:48 GMT
You need something like:
DECLARE @backup_date DATETIME
SET @backup_date = GETDATE()
SELECT
bs.database_name,
LTRIM( RIGHT( CONVERT( VARCHAR, bs.backup_start_date, 100 ), 7 ) ) AS
backup_start_time,
LTRIM( RIGHT( CONVERT( VARCHAR, bs.backup_finish_date, 100 ), 7 ) ) AS
backup_finish_time,
CAST( DATEDIFF( n, bs.backup_start_date, bs.backup_finish_date ) AS VARCHAR
) + ' min' AS diff,
avg_diffs.avg_diff
FROM msdb..backupset bs
INNER JOIN
(
SELECT
database_name,
CAST( AVG( DATEDIFF( n, backup_start_date, backup_finish_date ) ) AS
VARCHAR ) + ' min' AS avg_diff
FROM msdb..backupset
GROUP BY database_name
) avg_diffs ON bs.database_name = avg_diffs.database_name
WHERE YEAR( bs.backup_start_date ) = YEAR( @backup_date )
AND MONTH( bs.backup_start_date ) = MONTH( @backup_date )
AND DAY( bs.backup_start_date ) = DAY( @backup_date )
HTH
wBob
> Hi,
>
[quoted text clipped - 9 lines]
> Testdb2 10:15 PM 2 Min 5 Min 10:22 PM
> Testdb3 9:30 PM 120 Min 140 Min 12:50 AM