Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Programming / SQL / July 2008

Tip: Looking for answers? Try searching our database.

Need help on how to write this report.

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Lam Nguyen - 22 Jul 2008 16:18 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.