How to write script or a query that will display the last full backup and the last log backup for each database on each of the production servers using the system table "backupset" and related backup tables.
7 Answers
Something like this should get you the output that you're looking for. The below query pulls the most recent backup of type full, differential, or log backup for each database.
;with backup_cte as
(
select
database_name,
backup_type =
case type
when 'D' then 'database'
when 'L' then 'log'
when 'I' then 'differential'
else 'other'
end,
backup_finish_date,
rownum =
row_number() over
(
partition by database_name, type
order by backup_finish_date desc
)
from msdb.dbo.backupset
)
select
database_name,
backup_type,
backup_finish_date
from backup_cte
where rownum = 1
order by database_name;
I wrote a post on using backupset here if you are interested in more detail about the table. Simply though here is a query to pull what you want.
use msdb
go
-- D = Full, I = Differential and L = Log.
-- There are other types of backups too but those are the primary ones.
SELECT backupset.database_name,
MAX(CASE WHEN backupset.type = 'D' THEN backupset.backup_finish_date ELSE NULL END) AS LastFullBackup,
MAX(CASE WHEN backupset.type = 'I' THEN backupset.backup_finish_date ELSE NULL END) AS LastDifferential,
MAX(CASE WHEN backupset.type = 'L' THEN backupset.backup_finish_date ELSE NULL END) AS LastLog
FROM backupset
GROUP BY backupset.database_name
ORDER BY backupset.database_name DESC
Note: It was mentioned that I had joined to backmediaset and it wasn't needed. This is true and I can only assume that I forgot to remove it from the original query in the link. I've removed it from here.
You probably want to look at the msdb
database, for those details.
For instance:
DECLARE @DBName SYSNAME;
SET @DBName = DB_NAME(); -- modify these as you desire.
SET @DBName = NULL; -- comment this line if you want to limit the displayed history
SELECT DatabaseName = bs.database_name
, BackupStartDate = bs.backup_start_date
, CompressedBackupSize = bs.compressed_backup_size
, ExpirationDate = bs.expiration_date
, BackupSetName = bs.name
, RecoveryModel = bs.recovery_model
, ServerName = bs.server_name
, BackupType = CASE bs.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
ELSE '[unknown]' END
, LogicalDeviceName = bmf.logical_device_name
, PhysicalDeviceName = bmf.physical_device_name
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE (bs.database_name = @DBName
OR @DBName IS NULL)
AND bs.type = 'D'
ORDER BY bs.backup_start_date DESC;
for showing last full,Diff, Log backups for each database considering showing null if no backup is ever taken. using pivot (modified from source)
note that without joining to master.sys.databases and rely only on msdb.dbo.backupset you will not find the dbs which have no backups
SELECT name ,
recovery_model_desc ,
state_desc ,
d AS 'Last Full Backup' ,
i AS 'Last Differential Backup' ,
l AS 'Last log Backup'
FROM ( SELECT db.name ,
db.state_desc ,
db.recovery_model_desc ,
type ,
backup_finish_date
FROM master.sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset a ON a.database_name = db.name
) AS Sourcetable
PIVOT
( MAX(backup_finish_date) FOR type IN ( D, I, L ) ) AS MostRecentBackup
Based On @SQLBoy request we can write below query to include BackupSize and BackupSet.Name in this query. I omitted the pivoted data and make it simpler
WITH backupsetSummary
AS ( SELECT bs.database_name ,
bs.type bstype ,
MAX(backup_finish_date) MAXbackup_finish_date
FROM msdb.dbo.backupset bs
GROUP BY bs.database_name ,
bs.type
),
MainBigSet
AS ( SELECT db.name ,
db.state_desc ,
db.recovery_model_desc ,
bs.type ,
bs.name AS BackupSetName ,
bs.backup_size ,
bs.backup_finish_date
FROM master.sys.databases db
LEFT OUTER JOIN backupsetSummary bss ON bss.database_name = db.name
LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = db.name
AND bss.bstype = bs.type
AND bss.MAXbackup_finish_date = bs.backup_finish_date
)
SELECT *
FROM MainBigSet
-
Hi @imanabidi,can you please help me on adding two more columns to the code above you posted.I want backup_size and name from msdb.dbo.backupset table where msdb.dbo.backupset.name = 'XXXXXXX'.I need this because we are using a third party tool.That name is reflecting in msdb.dbo.backupset.name .Please let me know. Sep 1, 2016 at 11:17
I think below is the query for which we all have been looking:
Script or a query that will display the last full backup and the last log backup for each database on each of the production servers using the system table "backupset" and related backup tables.
-- Query to find latest "Full" as well as "T-Log" backup of all specified Databases
USE MSDB
GO
SELECT msdb.dbo.backupset.server_name
,msdb.dbo.backupset.database_name
,CASE msdb.dbo.backupset.[type] -- Let's decode the main types of backup here
WHEN 'D'
THEN 'Full'
WHEN 'I'
THEN 'Differential'
WHEN 'L'
THEN 'Transaction Log'
WHEN 'F'
THEN 'File or filegroup'
WHEN 'G'
THEN 'Differential file'
WHEN 'P'
THEN 'Partial'
WHEN 'Q'
THEN 'Differential partial'
ELSE msdb.dbo.backupset.[type]
END AS BackupType
,msdb.dbo.backupset.backup_start_date
,msdb.dbo.backupset.backup_finish_date
,CAST((CAST(DATEDIFF(s, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS INT)) / 3600 AS VARCHAR) + ' hours, ' + CAST((CAST(DATEDIFF(s, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS INT)) / 60 AS VARCHAR) + ' minutes, ' + CAST((CAST(DATEDIFF(s, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS INT)) % 60 AS VARCHAR) + ' seconds' AS [Total_Time_Taken_For_Backup]
,msdb.dbo.backupmediafamily.physical_device_name AS Backup_File_Location
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (
backup_set_ID IN (
SELECT MAX(backup_set_id)
FROM msdb.dbo.backupset
WHERE msdb.dbo.backupset.[type] = 'D'
GROUP BY database_name
)
OR backup_set_ID IN (
SELECT MAX(backup_set_id)
FROM msdb.dbo.backupset
WHERE msdb.dbo.backupset.[type] = 'L'
GROUP BY database_name
)
)
ORDER BY msdb.dbo.backupset.database_name
,BackupType
GO
you can use the following script:
1) if you want to get the latest backup only use the following command:
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name
reference: http://blog.sqlauthority.com/2010/11/04/sql-server-finding-last-backup-time-for-all-database/
2) if you want to get the backup history for all databases in the last seven days:
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
Last backup date for all databases. Simple.
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name