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 HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to obtain the last backup statistics for all databases?

Execute the following Microsoft SQL Server T-SQL example scripts in Management Studio Query Editor to lists the last backup date, backup size, duration and physical device name.

-- Microsoft SQL Server database backup information from msdb system database

-- T-SQL latest backup date - latest database backup

-- MSSQL stored procedure create - SQL select from select - left outer join - left join

CREATE PROCEDURE spLastDBBackupStatistics

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

 

-- T-SQL test stored procedure - exec stored procedure - execute sproc

EXEC spLastDBBackupStatistics 

GO

 

/* Partial results

 

DatabaseName            LastBackupDate          PhysicalDeviceName

AdventureWorks          2016-02-12 16:37:18.000 F:\sample\backup\AW.bak

AdventureWorks2008      2016-03-11 14:21:09.000 F:\sample\backup\AW8.bak

AdventureWorksDW        2016-02-12 16:41:24.000 F:\sample\backup\AWDW.bak

*/

------------

 

Exam Prep 70-461
Exam 70-461
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