There are mainly two types of backups:

• Native SQL backups (either stored to local disk or a network location)
• API backups, where software such as TSM/TDP or NetBackup, or CommVault, SQLLite, etc

There could be problems recovering a database if a server has different types of backups configured, unbeknown to the DBA. Per example, if TDP is used, and another DBA takes a local disk backup (out of band backup), it could pose a problem when we are restoring Tlog backups. This script identifies this situation, and lists those databases where different types of backups have been performed. If none are found, the result is blank

/*
DESCRIPTION
Queries the location of the last X days of databases backups
and reviews its location: API, DiskBackup or NetworkLocation

If backup location has changed in the last X days,
it returns those databases
If no change in location n the last X days,
it returns empty.

CONFIGURATION Set @days for the number of days to search for backups
Set @dbname for the specific database; leave NULL for all
 
Compatibility list:
MSSQL2012 MSSQL2005 MSSQL2008 MSSQL2000  
*/

 
DECLARE @days INT
DECLARE @dbname sysname
 
SET @days = 7			-- Number of days. Limited by the history stored on MSDB
SET @dbname = NULL		-- Specify database like 'database1' . If Null it will do all
 
 
 
 ---- End of configuration
 
declare @BackupLocation table
(i int identity(1,1) ,db varchar(1000) , bk_type varchar(10), bk_Loc varchar(3))

insert into @BackupLocation 

SELECT
 bs.database_name
, CASE bs.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
WHEN 'L' THEN 'TLog'
END AS backup_type
, left(bmf.physical_device_name,3)
 
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE 1=1
and bmf.family_sequence_number = '1'
and (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - @days)
--and bs.type = 'D'
and bs.database_name like coalesce(@dbname,'%')


ORDER BY
bs.database_name

--------------------------
declare @summary table

(i int identity(1,1) ,db varchar(1000), db2 varchar(1000) )

insert into @summary

select db, db+'_'+bk_type
 from @BackupLocation 

group by bk_loc, db, bk_type
order by db



 select 
 case
 when count(db2) > 1 then 'Mix Backups Found'
 else 'Only one Type of Backups'
 end as 'backups'
 , db2 from @Summary 
 
 group by db2
 having count(db2)>1 


select db,  bk_type,
case substring(bk_loc,2,1)
WHEN ':' THEN 'LOCAL DISK'
WHEN '\' THEN 'NETWORK LOCATION'  --'
ELSE 'API BACKUPS'
end as 'Type of Backups'

 from @BackupLocation 

where db in
(select db from @Summary 
 group by db2, db
 having count(db2)>1 
 )
 
group by bk_loc, db, bk_type
order by db

Thank you Andrey for the idea to create this script