[MS-SQL] 백업 기록 조회 sql

아래 쿼리를 이용해 백업기록을 조회할 수 있다.

-- backup type
/*
D : 데이터베이스 백업 ( Database backup )
I : 차등 백업 ( Differentail Database backup )
L : 트랜젝션 로그 백업 ( Transaction log backup )
F : 파일 또는 파일그룹 백업 ( File or filrgroup backup )
G : 차등 파일 백업 ( Differentail file backup )
P : 부분 백업 ( Partial backup )
Q : 차등 부분 백업 ( Differentail partial backup )
*/

 

select 
	backupset.database_name,
	backupset.backup_start_date,
	backupset.backup_finish_date,
	backupset.expiration_date,
	backupset.type as backup_type,
	round(backupset.backup_size/1024/1024/1024, 1) as backup_size_gb,		
	round(backupset.compressed_backup_size/1024/1024/1024, 1) as compressed_backup_size_gb,
	backupset.recovery_model,
	backupset.description,
	backupset.name as backup_name,
	media.physical_device_name,
	backupset.server_name,
	backupset.user_name
from msdb.dbo.backupset as backupset,
	msdb.dbo.backupmediafamily as media
where backupset.media_set_id = media.media_set_id
order by backupset.backup_finish_date desc;