CODEWITHSUNDEEP
sqlsql-server
deepti
deepti

Reputation: 729

need to perform join on queries using msdb database

i want to join 2 queries 

- give all database name except tempdb
select * from sys.sysdatabases where dbid <>4

output for first query example

    name    dbid    sid mode    status  status2 crdate  reserved    category    cmptlevel   filename    version
master  1   0x01    0   65544   1090520064  2003-04-08 09:13:36.390 1900-01-01 00:00:00.000 0   110 E:\Program Files\Microsoft SQL Server\MSSQL11.SQL372\MSSQL\DATA\master.mdf  706
model   3   0x01    0   65544   1090519040  2003-04-08 09:13:36.390 1900-01-01 00:00:00.000 0   110 E:\Program Files\Microsoft SQL Server\MSSQL11.SQL372\MSSQL\DATA\model.mdf   706
msdb    4   0x01    0   65544   1627390976  2012-02-10 21:02:17.770 1900-01-01 00:00:00.000 0   110 E:\Program Files\Microsoft SQL Server\MSSQL11.SQL372\MSSQL\DATA\MSDBData.mdf    706
UMRdb   5   0x01    0   65544   1627389952  2016-04-01 14:43:41.383 1900-01-01 00:00:00.000 0   100 E:\Program Files\Microsoft SQL Server\MSSQL11.SQL372\MSSQL\Data\UMRdb_data.mdf  706
BMCIMPACT   7   0x01    0   65544   1090519040  2016-08-28 08:11:35.023 1900-01-01 00:00:00.000 0   110 F:\Program Files\Microsoft SQL Server\MSSQL11.SQL372\MSSQL\Data\BMCIMPACT.mdf   706
ETXEXTDTP   8   0xD9B3D21CB279234BAEED922D086A16F1  0   65544   1090519040  2016-04-04 14:19:27.387 1900-01-01 00:00:00.000 0   110 F:\Program Files\Microsoft SQL Server\MSSQL11.SQL372\MSSQL\Data\ ETXEXTDTP.mdf  706
ETXSMILEP   9   0x01    0   65544   1090519040  2016-08-20 05:05:51.913 1900-01-01 00:00:00.000 0   110 F:\Program Files\Microsoft SQL Server\MSSQL11.SQL372\MSSQL\Data\ETXSMILEP_data.mdf  706


--give database name whose backup has been taken
    select bs.database_name,bs.backup_finish_date,bs.user_name,bmf.physical_device_name
    from msdb..backupset bs inner join msdb..backupmediafamily bmf
    on bs.media_set_id=bmf.media_set_id
    where bs.type='D' and bs.backup_finish_date>=GETDATE()-1
    order by bs.backup_finish_date desc

output for second query

ETXEXTDTP   2018-02-19 23:18:11.000 METNET\maestromet   J:\dump_data\AV-RISCVMSQL372\SQL372\db_dump\AV-RISCVMSQL372$SQL372_ETXEXTDTP_FULL_20180219_231552.bak
BMCIMPACT   2018-02-19 23:12:21.000 METNET\maestromet   J:\dump_data\AV-RISCVMSQL372\SQL372\db_dump\AV-RISCVMSQL372$SQL372_BMCIMPACT_FULL_20180219_230005.bak
msdb    2018-02-19 23:00:05.000 METNET\maestromet   J:\dump_data\AV-RISCVMSQL372\SQL372\db_dump\AV-RISCVMSQL372$SQL372_msdb_FULL_20180219_230005.bak
model   2018-02-19 23:00:05.000 METNET\maestromet   J:\dump_data\AV-RISCVMSQL372\SQL372\db_dump\AV-RISCVMSQL372$SQL372_model_FULL_20180219_230005.bak
master  2018-02-19 23:00:04.000 METNET\maestromet   J:\dump_data\AV-RISCVMSQL372\SQL372\db_dump\AV-RISCVMSQL372$SQL372_master_FULL_20180219_230004.bak

when we check second query database of one database has not been backed up

i need to join these 2 queries and need database name which does not come in second query mean database whose backup not taken

so there are 2 databases missinbg one is umrdb and other is ETXsMILEP i would like to get those two names in query.

Upvotes: 0

Views: 52

Answers (2)

Rigerta
Rigerta

Reputation: 4039

This could be what you need, a left join from your databases with the ones that have been backed up to only select the ones where no match exists, which means no backup has been taken, as below: 

with cte_backup as 
(
    select bs.database_name,bs.backup_finish_date,bs.user_name,bmf.physical_device_name
    from msdb..backupset bs inner join msdb..backupmediafamily bmf
    on bs.media_set_id=bmf.media_set_id
    where bs.type='D' and bs.backup_finish_date>=GETDATE()-1

) 
select s.* 
from sys.sysdatabases s left join cte_backup c on c.database_name = s.name
where s.dbid <> 4 
      and c.database_name is null -- no backup has been taken

Upvotes: 1

uzi
uzi

Reputation: 4146

If I understood correctly you need to use exists

select * 
from 
    sys.sysdatabases sd 
where 
    dbid <> 4
    and not exists (
        select 
            bs.database_name,bs.backup_finish_date,bs.user_name,bmf.physical_device_name
        from 
            msdb..backupset bs 
            inner join msdb..backupmediafamily bmf
        on bs.media_set_id=bmf.media_set_id
        where 
            bs.type='D' 
            and bs.backup_finish_date>=GETDATE()-1

            and bs.database_name = sd.name
    )

Upvotes: 1

Related Questions