DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
 
 
SITE SEARCH SQLUSA.com VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
How to list the last backup stats for all databases?

The following Microsoft SQL Server T-SQL stored procedure lists the last backup date, backup size, duration and physical device name.

CREATE PROCEDURE spLastDatabaseBackupStats

AS

  SELECT   DatabaseName = b.database_name,

           LastBackupDate = a.backup_date,

           PhysicalDeviceName = physical_device_name,

           BackupSizeMB = convert(INT,backup_size),

           DurationMinutes = duration

  FROM     (SELECT   sd.name                    AS database_name,

                     MAX(bs.backup_finish_date) AS backup_date

            FROM     MASTER.dbo.sysdatabases sd

                     LEFT OUTER JOIN msdb.dbo.backupset bs

                       ON sd.name = bs.database_name

                     LEFT OUTER JOIN (SELECT   sd.name       AS database_name,

                                      MAX(bs.backup_finish_date)   AS backup_date,

                                               bm.physical_device_name,

                                       bs.backup_size / 1024 / 1024  AS backup_size,

                                               DATEDIFF(mi,bs.backup_start_date,

                                               bs.backup_finish_date) AS duration

                                      FROM     MASTER.dbo.sysdatabases sd

                                               LEFT OUTER JOIN msdb.dbo.backupset bs

                                                 ON sd.name = bs.database_name

                                     LEFT OUTER JOIN msdb.dbo.backupmediafamily bm

                                                ON bm.media_set_id = bs.media_set_id

                                      GROUP BY sd.name,

                                               bm.physical_device_name,

                                               bs.backup_size / 1024 / 1024,

                                               DATEDIFF(mi,bs.backup_start_date,

                                               bs.backup_finish_date)) Summary

                       ON Summary.database_name = sd.name

                          AND Summary.backup_date = bs.backup_finish_date

            GROUP BY sd.name) a,

           (SELECT   sd.name                    AS database_name,

                     MAX(bs.backup_finish_date) AS backup_date,

                     Summary.physical_device_name,

                     Summary.backup_size,

                     Summary.duration

            FROM     MASTER.dbo.sysdatabases sd

                     LEFT OUTER JOIN msdb.dbo.backupset bs

                       ON sd.name = bs.database_name

                     LEFT OUTER JOIN (SELECT   sd.name         AS database_name,

                                 MAX(bs.backup_finish_date) AS backup_date,

                                               bm.physical_device_name,

                                  bs.backup_size / 1024 / 1024   AS backup_size,

                                               DATEDIFF(mi,bs.backup_start_date,

                                               bs.backup_finish_date) AS duration

                                      FROM     MASTER.dbo.sysdatabases sd

                                               LEFT OUTER JOIN msdb.dbo.backupset bs

                                                 ON sd.name = bs.database_name

                                      LEFT OUTER JOIN msdb.dbo.backupmediafamily bm

                                              ON bm.media_set_id = bs.media_set_id

                                      GROUP BY sd.name,

                                               bm.physical_device_name,

                                               bs.backup_size / 1024 / 1024,

                                               DATEDIFF(mi,bs.backup_start_date,

                                               bs.backup_finish_date)) Summary

                       ON Summary.database_name = sd.name

                          AND Summary.backup_date = bs.backup_finish_date

            GROUP BY sd.name,

                     bs.backup_finish_date,

                     Summary.physical_device_name,

                     Summary.backup_size,

                     Summary.duration) b

  WHERE    a.database_name = b.database_name

           AND a.backup_date = b.Backup_date

  ORDER BY DatabaseName

GO

EXEC spLastDatabaseBackupStats

/*

DBName      LastBackupDate          PhysicalDeviceName      MB    DurMin

....

HUGEAW2008  2010-01-25 04:09:02.000 F:\data\HAW8.bak        2496  1

Northwind   2009-08-23 07:01:23.000 F:\data\Northwind.bak   3     0

origAW2008  2010-02-25 19:43:31.000 F:\data\origAW8.bak     182   0

....

*/

 

Related articles:

 

http://www.sqlusa.com/bestpractices/lastbackupdate/

 

How to: Back Up a Database (SQL Server Management Studio)

 

Exam Prep 70-461
SQL 2016 DESIGN & PROGRAMMING
 
 
 
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE