La dernière séquence de sauvegardes

Vous faites vos sauvegardes (complètes, différentielles, journaux de transaction) sur des fichiers disque ?

Voici une requête pour récupérer, à partir de l'historique de sauvegarde de msdb, la séquence complète des sauvegardes les plus récentes à restaurer. Utile pour une migration par restauration, par exemple...
C'est cette requête (mise en vue dans msdb) qui était utilisée pour la démo de migration SQL 2016 du SQL Saturday...
-- Last Full Backup
SELECT database_name
,backup_start_date
,backup_finish_date
,duration
,type
,name
,compressed_backup_size
,physical_device_name
FROM ( SELECT bk.database_name
,bk.backup_start_date
,bk.backup_finish_date
,DATEDIFF(SECOND, bk.backup_start_date,
bk.backup_finish_date) AS duration
,bk.type
,bk.name
,bk.compressed_backup_size
,bm.physical_device_name
,ROW_NUMBER() OVER ( PARTITION BY bk.database_name ORDER BY bk.backup_finish_date DESC ) AS RowNumber
FROM msdb..backupset bk
JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id
WHERE bk.type = 'D'
AND DB_ID(bk.database_name) IS NOT NULL -- test d'existence de la base
AND bm.device_type = 2 -- pour fichiers disque
) AS FB
WHERE RowNumber = 1
UNION ALL
-- Last Incremental Backup (after last full backup)
SELECT IBA.database_name
,IBA.backup_start_date
,IBA.backup_finish_date
,IBA.duration
,IBA.type
,IBA.name
,IBA.compressed_backup_size
,IBA.physical_device_name
FROM ( SELECT bk.database_name
,bk.backup_start_date
,bk.backup_finish_date
,DATEDIFF(SECOND, bk.backup_start_date,
bk.backup_finish_date) AS duration
,bk.type
,bk.name
,bk.compressed_backup_size
,bm.physical_device_name
,ROW_NUMBER() OVER ( PARTITION BY bk.database_name ORDER BY bk.backup_finish_date DESC ) AS RowNumber
FROM msdb..backupset bk
JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id
WHERE bk.type = 'I'
AND DB_ID(bk.database_name) IS NOT NULL -- test if database exists
AND bm.device_type = 2 -- disk files
) AS IBA
JOIN ( SELECT *
FROM ( SELECT bk.database_name
,bk.backup_finish_date
,ROW_NUMBER() OVER ( PARTITION BY bk.database_name ORDER BY bk.backup_finish_date DESC ) AS RowNumber
FROM msdb..backupset bk
JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id
WHERE bk.type = 'D'
AND DB_ID(bk.database_name) IS NOT NULL -- test if database exists
AND bm.device_type = 2 -- disk files
) AS FB
WHERE RowNumber = 1
) AS LFB ON IBA.database_name = LFB.database_name
AND IBA.backup_finish_date > LFB.backup_finish_date
WHERE IBA.RowNumber = 1
UNION ALL

-- Transaction logs backups after the last Full or Incremental Backup
SELECT TLB.database_name
,TLB.backup_start_date
,TLB.backup_finish_date
,TLB.duration
,TLB.type
,TLB.name
,TLB.compressed_backup_size
,TLB.physical_device_name
FROM ( SELECT database_name
,backup_start_date
,backup_finish_date
,DATEDIFF(SECOND, bk.backup_start_date,
bk.backup_finish_date) AS duration
,type
,name
,compressed_backup_size
,physical_device_name
FROM msdb..backupset bk
JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id
WHERE bk.type = 'L'
AND DB_ID(bk.database_name) IS NOT NULL -- test if database exists
AND bm.device_type = 2 -- disk files
) AS TLB
JOIN (
-- Last Database backup, Full or incremental (if any) LFIB
SELECT database_name
,backup_finish_date
,type
FROM ( SELECT bk.database_name
,bk.backup_finish_date
,bk.type
,ROW_NUMBER() OVER ( PARTITION BY bk.database_name ORDER BY bk.backup_finish_date DESC ) AS RowNumber
FROM msdb..backupset bk
JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id
WHERE bk.type IN ( 'D', 'I' )
AND DB_ID(bk.database_name) IS NOT NULL -- test if database exists
AND bm.device_type = 2 -- disk files
) AS FB
WHERE RowNumber = 1
) AS LFIB ON TLB.database_name = LFIB.database_name
AND TLB.backup_finish_date > LFIB.backup_finish_date
ORDER BY database_name ASC
,backup_finish_date ASC;