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.
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.
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:
;WITH database_files AS
SELECT DatabaseName = d.name
, FileName = mf.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)
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
, FileRoot = df.FileRoot
, FileType = df.FileType
, 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
(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'
(df.recovery_model_desc = N'FULL' AND df.FileType = N'LOG')
OR df.FileType <> N'LOG'
ORDER BY df.DatabaseName
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!
Let me know if you have any questions or comments about this post.
This post is part of our series on recovery.