• Article Topics

  • Become a member of the SQLDBASupport community and receive notifications of new posts by email

    Join 82 other followers

  • Are you writer? Become an Author! Mail to

    amitvaid81 @ gmail dot com

  • Advertisements

SQL Databases Last Backup Data and Time with Recovery Mode

Many times DBAs find difficulty to find out when did Last full backup or Transaction log backup completed on SQL Instance and If the SQL Instance has number of database its even more difficult to find out through SQL Enterprise Manager or Management Studio.

Following query display the Last Full and Transaction log backup Data/Time and with recovery model.

set nocount on

IF EXISTS(SELECT name FROM sysobjects

      WHERE name = ‘DummyTbl’)– AND type = ‘P’)

   drop table DummyTbl


 select cast(@@servername as varchar(30)) as ‘Server’  ,

      ‘Name’ = name, ‘Recovery’ =DATABASEPROPERTYEX(b.name,’Recovery’),

      ‘Full Backup’ = max(FULLB),

      ‘TLog Backup’ = max(TLogB)    into DummyTbl    


            (SELECT database_name,–type,

            case when type = ‘D’ THEN max(backup_finish_date)

            end as ‘FULLB’,

            case when type = ‘L’ THEN max(backup_finish_date)

            end as ‘TLogB’

            FROM msdb..backupset a

                  where a.type in (‘D’,’L’)

                  group by a.type,database_name)

                        as  ViewS   right outer join master..sysdatabases b

                        on b.name = database_name     group BY name


select * from DummyTbl

      where name not in (‘tempdb’,’northwind’,’pubs’) order by 4 


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: