This script is used to verify a backup chain. It is based on Robert Davis’ script. It gets the most recent full backup, the most recent differential backup based upon that full, and then all transaction log backups since the latter of those two backups. It calculates whether there is a broken transaction log chain, and also whether the backup files are in the location where they were backed up to.
|
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 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 |
-- adapted from Robert Davis (@SQLSoldier) -- //www.sqlsoldier.com/wp/sqlserver/day3of31daysofdisasterrecoverydeterminingfilestorestoredatabase -- todo: if default file is not unique (backups appended), check for this backup by position in the backup file. SET NOCOUNT ON; IF OBJECT_ID('tempdb..#Backups','U') IS NOT NULL DROP TABLE #Backups; IF OBJECT_ID('tempdb..#BackupFiles','U') IS NOT NULL DROP TABLE #BackupFiles; CREATE TABLE #Backups ( BakID INTEGER IDENTITY(1, 1) NOT NULL PRIMARY KEY, database_name SYSNAME, backup_set_id INTEGER NOT NULL, media_set_id INTEGER NOT NULL, first_family_number TINYINT NOT NULL, last_family_number TINYINT NOT NULL, first_lsn NUMERIC(25, 0) NULL, last_lsn NUMERIC(25, 0) NULL, database_backup_lsn NUMERIC(25, 0) NULL, backup_finish_date DATETIME NULL, type CHAR(1) NULL, family_sequence_number TINYINT NOT NULL, physical_device_name NVARCHAR(260) NOT NULL, device_type TINYINT NULL, position INTEGER NULL, is_backup_chain_broken BIT NOT NULL DEFAULT 0, is_backup_file_present BIT NOT NULL DEFAULT 0, physical_path_name NVARCHAR(260) NULL, physical_file_name NVARCHAR(260) ); CREATE INDEX IX1 ON #Backups (database_name, database_backup_lsn); CREATE INDEX IX2 ON #Backups ([type], database_name, last_lsn); CREATE INDEX IX3 ON #Backups (database_name, last_lsn); CREATE INDEX IX4 ON #Backups (database_name, [type]) INCLUDE (first_lsn, last_lsn); CREATE INDEX IX5 ON #Backups (physical_path_name) INCLUDE (physical_file_name, BakID); -- Get the most recent full backup with all backup files WITH cte AS ( SELECT B.database_name, B.backup_set_id, B.media_set_id, B.first_family_number, B.last_family_number, B.first_lsn, B.last_lsn, B.database_backup_lsn, B.backup_finish_date, B.type, B.position, BF.family_sequence_number, BF.physical_device_name, BF.device_type, RN = ROW_NUMBER() OVER (PARTITION BY B.database_name ORDER BY B.backup_finish_date DESC, B.backup_set_id) FROM msdb.dbo.backupset AS B JOIN msdb.dbo.backupmediafamily AS BF ON BF.media_set_id = B.media_set_id AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_number WHERE B.is_copy_only = 0 AND B.type = 'D' -- FULL database backup AND BF.physical_device_name NOT IN ('Nul', 'Nul:') AND BF.device_type <> 7 -- virtual device type - can you restore from one of these? ) INSERT INTO #Backups ( database_name, backup_set_id, media_set_id, first_family_number, last_family_number, first_lsn, last_lsn, database_backup_lsn, backup_finish_date, type, position, family_sequence_number, physical_device_name, device_type, physical_path_name, physical_file_name) SELECT database_name, backup_set_id, media_set_id, first_family_number, last_family_number, first_lsn, last_lsn, database_backup_lsn, backup_finish_date, type, position, family_sequence_number, physical_device_name, device_type, REVERSE(SUBSTRING(ca.rfile, ISNULL(NULLIF(CHARINDEX('\', ca.rfile),0),1), 8000)), REVERSE(LEFT(ca.rfile, ISNULL(NULLIF(CHARINDEX('\', ca.rfile)-1, -1), LEN(ca.rfile)))) FROM cte CROSS APPLY (SELECT rfile = REVERSE(physical_device_name)) ca WHERE RN = 1; -- Get the most recent differential backup based on that full backup WITH cte AS ( SELECT B.database_name, B.backup_set_id, B.media_set_id, B.first_family_number, B.last_family_number, B.first_lsn, B.last_lsn, B.database_backup_lsn, B.backup_finish_date, B.type, B.position, BF.family_sequence_number, BF.physical_device_name, BF.device_type, RN = ROW_NUMBER() OVER (PARTITION BY B.database_name ORDER BY B.backup_finish_date DESC, B.backup_set_id) FROM msdb.dbo.backupset AS B JOIN #Backups baks ON baks.database_name = B.database_name -- Get the lsn that the differential backups, if any, will be based on AND baks.database_backup_lsn = B.database_backup_lsn JOIN msdb.dbo.backupmediafamily AS BF ON BF.media_set_id = B.media_set_id AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_number WHERE B.is_copy_only = 0 AND B.type = 'I' -- DIFFERENTIAL database backup AND BF.physical_device_name NOT IN ('Nul', 'Nul:') ) INSERT INTO #Backups( database_name, backup_set_id, media_set_id, first_family_number, last_family_number, first_lsn, last_lsn, database_backup_lsn, backup_finish_date, type, position, family_sequence_number, physical_device_name, device_type, physical_path_name, physical_file_name) SELECT database_name, backup_set_id, media_set_id, first_family_number, last_family_number, first_lsn, last_lsn, database_backup_lsn, backup_finish_date, type, position, family_sequence_number, physical_device_name, device_type, REVERSE(SUBSTRING(ca.rfile, ISNULL(NULLIF(CHARINDEX('\', ca.rfile),0),1), 8000)), REVERSE(LEFT(ca.rfile, ISNULL(NULLIF(CHARINDEX('\', ca.rfile)-1, -1), LEN(ca.rfile)))) FROM cte CROSS APPLY (SELECT rfile = REVERSE(physical_device_name)) ca WHERE RN = 1; -- Get all log backups where the last_lsn is >= the last_lsn for this db's full/diff b/u. WITH cte AS ( SELECT database_name, last_lsn = MAX(last_lsn) FROM #Backups GROUP BY database_name ) INSERT INTO #Backups ( database_name, backup_set_id, media_set_id, first_family_number, last_family_number, first_lsn, last_lsn, database_backup_lsn, backup_finish_date, type, position, family_sequence_number, physical_device_name, device_type, physical_path_name, physical_file_name) SELECT B.database_name, B.backup_set_id, B.media_set_id, B.first_family_number, B.last_family_number, B.first_lsn, B.last_lsn, B.database_backup_lsn, B.backup_finish_date, B.type, B.position, BF.family_sequence_number, BF.physical_device_name, BF.device_type, REVERSE(SUBSTRING(ca.rfile, ISNULL(NULLIF(CHARINDEX('\', ca.rfile),0),1), 8000)), REVERSE(LEFT(ca.rfile, ISNULL(NULLIF(CHARINDEX('\', ca.rfile)-1, -1), LEN(ca.rfile)))) FROM msdb.dbo.backupset B JOIN cte baks ON baks.database_name = B.database_name AND baks.last_lsn <= B.last_lsn JOIN msdb.dbo.backupmediafamily AS BF ON BF.media_set_id = B.media_set_id AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_number CROSS APPLY (SELECT rfile = REVERSE(BF.physical_device_name)) ca WHERE B.is_copy_only = 0 AND B.type = 'L' -- Transaction Log backups AND BF.physical_device_name NOT IN ('Nul', 'Nul:') --AND @DBBackupLSN BETWEEN B.first_lsn AND B.last_lsn ORDER BY B.database_name, B.last_lsn, B.backup_finish_date, B.backup_set_id; -- mark any tlogs if the log backup chain is broken -- this only marks the one that is immediately after the break -- if you desire, you can delete this and the following to only show the recoverable files WITH cte (database_name, last_lsn) AS ( -- get the latest lsn that is in a full/diff backup per database SELECT database_name, MAX(last_lsn) FROM #Backups WHERE type LIKE '[DI]' GROUP BY database_name ), cte2 (database_name, BakID) AS ( -- get any log backups where: -- 1. that backups first_lsn is not equal to a last_lsn for another log backup (should be the previous backup) -- 2. that backup is not the first log backup, where the max from the full/diff will be between it's first_lsn / last_lsn SELECT t1.database_name, MIN(t1.BakID) FROM #Backups t1 LEFT JOIN cte ON cte.database_name = t1.database_name WHERE t1.type = 'L' AND t1.first_lsn NOT IN (SELECT last_lsn FROM #Backups t2 WHERE t1.database_name = t2.database_name AND type = 'L') AND cte.last_lsn NOT BETWEEN t1.first_lsn AND t1.last_lsn GROUP BY t1.database_name ) UPDATE t1 SET is_backup_chain_broken = 1 FROM #Backups t1 JOIN cte2 ON t1.database_name = cte2.database_name AND t1.BakID = cte2.BakID; -- get each unique directory that the backup files were created in -- For each directory, -- get list of all of the files in the directory -- check for missing backup files CREATE TABLE #BackupFiles ( BackupFilesID INTEGER IDENTITY PRIMARY KEY NONCLUSTERED, subdirectory NVARCHAR(260), depth SMALLINT, is_file BIT, UNIQUE CLUSTERED (subdirectory, BackupFilesID) ); DECLARE @physical_path_name NVARCHAR(260), @cmd NVARCHAR(1000); DECLARE cFilePaths CURSOR FAST_FORWARD READ_ONLY FOR SELECT DISTINCT physical_path_name FROM #Backups; OPEN cFilePaths FETCH NEXT FROM cFilePaths INTO @physical_path_name; WHILE @@FETCH_STATUS = 0 BEGIN TRUNCATE TABLE #BackupFiles; -- get the list of files in this directory, put into temp table. SET @cmd = N'EXECUTE xp_dirtree N''' + @physical_path_name + N''',1,1;'; INSERT INTO #BackupFiles EXECUTE (@cmd); -- update the files that are present UPDATE t1 SET is_backup_file_present = 1 FROM #Backups t1 JOIN #BackupFiles t2 ON t1.physical_file_name = t2.subdirectory WHERE t1.physical_path_name = @physical_path_name FETCH NEXT FROM cFilePaths INTO @physical_path_name; END CLOSE cFilePaths; DEALLOCATE cFilePaths; SELECT database_id = DB_ID(database_name), * FROM #Backups ORDER BY database_name, BakID; |
