Here is a quick script to see the last backup for each database on the server. Gives file name (so you know where they are going), whether the database is a current database (could have been dropped in the past), and the recovery model.
Should give you an idea of the basics.
/* ========================================================================================================================== */
/* Get the very last backup set (Database and all Tranlog backups) for each database in instance */
/* ========================================================================================================================== */
CREATE TABLE #dummybackups
(
DBNamevarchar(300),
LastBackupDatedatetime
)
CREATE TABLE #dummydb
(
DBNvarchar(300),
RecModvarchar(50)
)
INSERT INTO #dummydb
SELECT name, recovery_model_desc FROM master.sys.databases
WHERE name NOT IN ('tempdb') AND is_read_only = 0 AND state = 0
INSERT INTO #dummybackups
SELECT DISTINCT database_name, NULL FROM msdb..backupset ORDER BY database_name
--Get last full backup for each database and put in dummy table for that database
UPDATE #dummybackups
SET LastBackupDate = (SELECT TOP 1 backup_start_date FROM msdb..backupset WHERE database_name = DBName AND type = 'D' ORDER BY backup_start_date DESC)
SELECT S.database_name,
CONVERT(varchar(20),'') AS CurrentDB,
S.recovery_model, S.user_name,
S.backup_start_date,
CASE WHEN CONVERT(varchar(200),GETDATE(),101) = CONVERT(varchar(200),S.backup_start_date,101) THEN '' ELSE ' * ' END AS NotFromToday,
S.type,
CONVERT(decimal(10,2),S.backup_size/1024.0/1024.0) AS backup_size_MB, M.physical_device_name
INTO #dummybackups2
FROM msdb..backupset S
JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
WHERE S.backup_start_date >= (SELECT LastBackupDate FROM #dummybackups WHERE database_name = DBName) AND (S.recovery_model IS NOT NULL)
--AND S.database_name = 'somedbname'
ORDER by S.database_name, backup_start_date DESC
UPDATE #dummybackups2
SET CurrentDB = 'NO'
WHERE NOT EXISTS(SELECT name FROM master.sys.databases WHERE master.sys.databases.name = database_name)
-- insert into final resultset the databases that are in the instance, but don't have a record in the backups
INSERT INTO #dummybackups2
SELECT DBN,'',RecMod,'',NULL,'','',0,NULL FROM #dummydb WHERE DBN NOT IN (SELECT database_name FROM #dummybackups2)
SELECT *
,ISNULL(SUBSTRING(physical_device_name,1,LEN(physical_device_name) - PATINDEX('%\%',REVERSE(physical_device_name)) + 1),NULL) AS PathName
,ISNULL(SUBSTRING(physical_device_name,LEN(physical_device_name) - PATINDEX('%\%',REVERSE(physical_device_name)) + 2,PATINDEX('%\%',REVERSE(physical_device_name))-1),NULL) AS FileNm
FROM #dummybackups2 ORDER BY database_name, backup_start_date DESC
DROP TABLE #dummybackups
DROP TABLE #dummybackups2
DROP TABLE #dummydb