Vous êtes ici :
Ce script crée une vue V_LastDiskBackupSequence dans la base msdb.
Cette vue produit la liste des backups de la dernière séquence (backups Full, différential, log) effectués sur disque pour une (ou plusieurs) bases de données, et génère les commandes de restore.
Testé avec SQL Server 2008 et au delà.
Utiliser avec le SELECT suivant :
|
1 2 3 4 |
SELECT * FROM msdb.dbo.V_LastDiskBackupSequence WHERE database_name = 'MyDB' -- facultatif ORDER BY database_name ASC, backup_sequence; |
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 |
/*------------------------------------------------------------------- [SCRIPT] view msdb.V_LastDiskBackupSequence [DATABASE] msdb [DESCRIPTION] Get the last sequence of disk database backups (Full,Diff,Log) with restore commands generation [AUTHOR] DATAFLY - Arian Papillon [USAGE] SELECT * FROM msdb.dbo.V_LastDiskBackupSequence WHERE database_name = 'MyDB' ORDER BY database_name ASC, backup_sequence; -------------------------------------------------------------------*/ USE [msdb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /****** Object: View [dbo].[V_LastDiskBackupSequence] ******/ CREATE -- OR ALTER VIEW [dbo].[V_LastDiskBackupSequence] AS -- Last Full Backup SELECT ROW_NUMBER() OVER ( PARTITION BY database_name ORDER BY backup_finish_date ASC) AS backup_sequence , database_name , backup_set_id , backup_set_uuid , media_set_id , backup_start_date , backup_finish_date , duration , type , name , compressed_backup_size , physical_device_name , position , CASE WHEN ROW_NUMBER() OVER ( PARTITION BY database_name ORDER BY backup_finish_date DESC ) = 1 THEN REPLACE(restorecmd, 'NORECOVERY', 'RECOVERY') ELSE restorecmd END AS restorecmd FROM ( SELECT database_name , backup_set_id , backup_set_uuid , media_set_id , backup_start_date , backup_finish_date , duration , type , name , compressed_backup_size , physical_device_name , position , 'RESTORE DATABASE [' + FB.database_name + '] FROM DISK=''' + FB.physical_device_name + ''' WITH FILE = ' + CAST(position AS VARCHAR(3)) + ', NORECOVERY;' AS restorecmd FROM ( SELECT bk.database_name , bk.backup_set_id , bk.backup_set_uuid , bk.media_set_id , 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 , bk.position , 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 IN (2,102) -- pour fichiers disque ) AS FB WHERE RowNumber = 1 UNION ALL -- Last Incremental Backup (after last full backup) SELECT IBA.database_name , IBA.backup_set_id , IBA.backup_set_uuid , IBA.media_set_id , IBA.backup_start_date , IBA.backup_finish_date , IBA.duration , IBA.type , IBA.name , IBA.compressed_backup_size , IBA.physical_device_name , IBA.position , 'RESTORE DATABASE [' + IBA.database_name + '] FROM DISK=''' + IBA.physical_device_name + ''' WITH FILE = ' + CAST(position AS VARCHAR(3)) + ', NORECOVERY;' AS restorecmd FROM ( SELECT bk.database_name , bk.backup_set_id , bk.backup_set_uuid , bk.media_set_id , 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 , bk.position , 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 IN (2,102) -- 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 IN (2,102) -- 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_set_id , TLB.backup_set_uuid , TLB.media_set_id , TLB.backup_start_date , TLB.backup_finish_date , TLB.duration , TLB.type , TLB.name , TLB.compressed_backup_size , TLB.physical_device_name , TLB.position , 'RESTORE LOG [' + TLB.database_name + '] FROM DISK=''' + TLB.physical_device_name + ''' WITH FILE = ' + CAST(position AS VARCHAR(3)) + ', NORECOVERY;' AS restorecmd FROM ( SELECT database_name , backup_set_id , backup_set_uuid , bk.media_set_id , 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 , bk.position 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 IN (2,102) -- 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 IN (2,102) -- 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 ) seq GO |
Table of Contents