Member of the LearnItFirst.com Video Training Network | LearnSqlServer.com | SQL SSIS Training | SQL Programming Tutorials |
LearnSqlServer.com Forums LearnSqlServer.com
Welcome Guest Search | New Posts | Members | Log In | Register

View backup history, size and predict size of database for future Options
Scott Whigham
Posted: Tuesday, August 29, 2006 9:07:13 AM


Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX
Code:

CREATE FUNCTION dbo.GetBackupHistory(@DbName SYSNAME)
RETURNS TABLE
AS
/*
    Author: Scott Whigham from http://www.LearnSqlServer.com/

    Description: This script returns the list of all backups taken of @DbName including the sizes (MB and GB)
        and duration (in seconds and minutes).

    Misc Notes: One way, in the absence of other information, to predict future sizing of a database is to look at
        how the database backups have grown. If you'll take the full backups and look at the growth percentage
        over several months, that can give you a relatively accurate portrayal of your percent growth for future months.
   
    Versions: SQL Server 2005, 2000, 7.0
   
    Creation Date: August 29, 2006

    For more scripts like this one, visit http://forums.learnsqlserver.com/codesamples.aspx
*/
RETURN (
    SELECT bs.database_name AS DbName
        , bs.type AS BackupType -- 'D' is a FULL backup, 'L' is log, and 'I' is DIFFERENTIAL (of course!)
        , CAST ( bs.backup_size / 1024.0 / 1024 AS DECIMAL(10, 2) ) AS BackupSize_MB
        , CAST ( bs.backup_size / 1024.0 / 1024 / 1024 AS DECIMAL(10, 2) ) AS BackupSize_GB
        , bs.backup_start_date AS Backup_StartDate
        , bs.backup_finish_date AS Backup_EndDate
        , DATEDIFF ( ss, bs.backup_start_date, bs.backup_finish_date ) AS TookHowLong_Seconds
        -- We don't use DATEDIFF(minute...) here because of how integer-only math works
        , CAST ( DATEDIFF ( ss, bs.backup_start_date, bs.backup_finish_date ) / 60.0 AS DECIMAL(10,2) ) AS TookHowLong_Minutes
        , bs.name AS BackupName
        , bs.description AS BackupDescription
        , bs.[user_name] AS UserNameTakingBackup
        , CAST(bs.software_major_version AS VARCHAR) + '.' + CAST(bs.software_minor_version AS VARCHAR) + '.'
            + CAST(bs.software_build_version AS VARCHAR) AS SoftwareBuildVersion
        , bs.server_name AS ServerName
        , bs.machine_name AS MachineName
        , bs.collation_name AS Collation
        , bmf.physical_device_name AS DeviceName
    FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf
        ON bs.media_set_id = bmf.media_set_id
    WHERE bs.database_name = @DbName
)
GO
SELECT * FROM GetBackupHistory('msdb') ORDER BY Backup_StartDate DESC
Users browsing this topic
Guest


Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.
     
Don't Forget!
LearnItFirst.com
Don't Forget!
LearnWindows2003.com
 
Home | About Us | Support | Contact Us | Privacy | Site Map | Blogs Blogs Refer a Friend and Get a Free Subscription!
© Copyright 2004-2007 LearnItFirst.com LLC. All rights reserved. All trademarks remain the property of their respective owners.
This site is not affiliated in any way with the Microsoft Corporation.