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    

      from

            (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 

Advertisements

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: