Storing Backups Locally? You have no Backups!
Me, being nice!
You’ve got business critical data stored in your SQL Server, don’t you? And you’re taking backups of those databases, right? But where are you storing those backups? If you’re storing backups locally, on the same media as the database itself, you’re asking for a world of hurt should something happen to that media. For instance, what if one of the disks fails where your storing both the database and its backup file? No more data. This applies even if you’re storing databases and their backups on a SAN; if the SAN has a problem you won’t be able to access either the database, or its backups.
Austin Powers needs no backup!
It’s a really great idea to store backups offsite to enable recovery from a complete disaster, such as when a hacker miscreant ex-employee destroys your servers, and all the backups. However, before you can run, you must learn to walk.
tl/dr;
This post provides a script showing the list of databases with the most recent backup listed. The LocalBackup column shows an X for backups taken to local media; consider modifying these backups to point to physically different media, such as a file server, or external disk. As pointed out above, storing backups locally is a bad idea. The script actually compares the physical filename of the database files to the physical filename of the database backups. The script compares files stored on both drive letters and UNC file shares.
Databases in full recovery model will show two rows, one for the data, and one for the log, providing details about both the most recent database backup, and the most recent log backup. Databases in simple recovery model will only show a single row, for database backups, since you can’t take a log backup for databases in simple recovery mode.
In case you don’t know how to take database backups in SQL Server, check the Microsoft Documentation for an overview.
Paying the rent!
Anyway, onto the raison d’être for this post, the script:
|
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 |
;WITH database_files AS ( SELECT DatabaseName = d.name , d.recovery_model_desc , FileName = mf.name , mf.physical_name , FileType = mf.type_desc , FileType1 = mf.type , FileRoot = CASE WHEN mf.physical_name LIKE N'_:\%' THEN LEFT(mf.physical_name, 2) ELSE CASE WHEN LEFT(mf.physical_name, 2) = N'\\' THEN LEFT(mf.physical_name, CHARINDEX(N'\', mf.physical_name, CHARINDEX(N'\', mf.physical_name, 3) + 1) - 1) ELSE N'' END END FROM sys.databases d INNER JOIN sys.master_files mf ON d.database_id = mf.database_id WHERE d.state_desc = N'ONLINE' ) , src AS ( SELECT DatabaseName = bs.database_name , BackupStartDate = bs.backup_start_date --, CompressedBackupSizeMB = bs.compressed_backup_size / 1048576 , ExpirationDate = bs.expiration_date , BackupSetName = bs.name , RecoveryModel = bs.recovery_model , ServerName = bs.server_name , BackupType = CASE bs.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' WHEN 'I' THEN 'Differential Database' WHEN 'F' THEN 'File or Filegroup' WHEN 'G' THEN 'Differential File' WHEN 'P' THEN 'Partial' WHEN 'Q' THEN 'Differential Partial' ELSE '[unknown]' END , BackupTypeC = bs.type , LogicalDeviceName = bmf.logical_device_name , PhysicalDeviceName = bmf.physical_device_name , rn = ROW_NUMBER() OVER (PARTITION BY bs.database_name, bs.type ORDER BY bs.backup_start_date DESC) FROM msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] ) SELECT ServerName = @@SERVERNAME , df.DatabaseName , df.FileName , FileRoot = df.FileRoot , FileType = df.FileType , src.BackupStartDate , src.RecoveryModel , src.BackupType , src.PhysicalDeviceName , LocalBackup = CASE WHEN src.PhysicalDeviceName LIKE (df.physical_name + N'%') THEN 'X' ELSE '' END FROM database_files df LEFT JOIN src ON df.DatabaseName = src.DatabaseName AND ( (CASE WHEN df.FileType = N'ROWS' AND src.BackupTypeC IN ('D', 'I', 'F', 'G', 'P', 'Q') THEN 1 END = 1) OR (CASE WHEN df.FileType = N'LOG' AND src.BackupTypeC = 'L' THEN 1 END = 1) ) WHERE (src.rn = 1 OR src.rn IS NULL) AND (src.ServerName = @@SERVERNAME OR src.ServerName IS NULL) --filter to local server only AND df.DatabaseName <> N'tempdb' AND ( (df.recovery_model_desc = N'FULL' AND df.FileType = N'LOG') OR df.FileType <> N'LOG' ) ORDER BY df.DatabaseName , df.FileType1; |
Results include the server name in case you want to run this against multiple SQL Server instances. If the BackupStartDate, and other backup-related columns contain NULL values, you’ve probably never taken a backup of the database, and should consider doing so immediately, if you value the data stored therein!
Exit, post-haste!
Let me know if you have any questions or comments about this post.
This post is part of our series on recovery.
[…] the transaction log on the same physical medium as the database data files. Make sure your database backups are stored separately, too. Having the log, data, and backups on separate drives decreases the possibility of losing data from […]