LearnItFirst User Forum

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