All database administrators know that safeguarding data is paramount and having a strong restore strategy is crucial. In this post, I would like to add another tool to your arsenal that I have found helpful in auditing server health and configurations as it pertains to backups and the backup chain.
A backup chain is important when it comes to recovery. If you have full, diff, and log backups going to different backup locations, non-copy-only off-cycle backups from developers or testers, or other strangeness in your server’s backup history, you could be in a world of hurt should you need to restore to a specific time.
The script below will look through msdb and add up the number of each type of backup found and count how many are going to different locations, separated by native to a network share, third party via GUID, or native to a local disk location.
/*###########################################################################*/
/*# Created by David Seis #*/
/*# 4/18/2023 #*/
/*# #*/
/*# Straight Path IT Solutions, LLC. #*/
/*###########################################################################*/
/*
Description:
- This identifies the number of sql server backups detected in the time period from the backup history log in msdb, breaks it down by distinct locations found, how many native share, native local, and third party guid backups found. the second result set outputs each found unique backup location per database and backup type.
- 4/19/2023 - counting third party tools as 1 location
*/
declare @days int = 7,
@length tinyint = 20,
@copyonly bit = 0;
SELECT
@@Servername as [Server Name]
, b.database_name
, b.server_name as 'Source_Server'
, CASE WHEN COUNT(DISTINCT f.physical_device_name) IS NULL THEN 0
ELSE COUNT(DISTINCT f.physical_device_name)
END AS [#FULLs Found]
, COUNT(DISTINCT CASE WHEN f.physical_device_name LIKE '%:\%'
OR f.physical_device_name LIKE '\\%'
THEN LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length)
ELSE '1' END) AS [Distinct FULL locations. If > 1, check the chain!]
, Count(DISTINCT CASE WHEN f.physical_device_name LIKE '\\%'
THEN LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length) END) as [Network Share Locations (Native)]
, Count(DISTINCT CASE WHEN f.physical_device_name LIKE '{%'
AND f.physical_device_name NOT LIKE '%:\%'
AND f.physical_device_name NOT LIKE '\\%'
OR f.physical_device_name LIKE '%{%'
THEN f.physical_device_name END) as [Third Party tool]
, Count(DISTINCT CASE WHEN f.physical_device_name LIKE '%:\%'
THEN LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length) END) as [Local Drive (Native)]
FROM msdb.dbo.backupmediafamily f
INNER JOIN msdb.dbo.backupset b ON f.media_set_id = b.media_set_id
INNER JOIN sys.databases s on b.database_name = s.name
WHERE b.type in ('D') and s.database_id > 4
AND (b.backup_start_date >=CONVERT(VARCHAR(10),dateadd(day,-@days,getdate()),120))
AND is_copy_only = @copyonly
GROUP BY b.database_name, b.server_name
ORDER BY COUNT(DISTINCT CASE WHEN LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length)
LIKE '{%'OR LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length) like '%-%-%'
AND LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length) NOT LIKE '%:\%'
THEN '1' ELSE LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length) END) desc
SELECT
@@Servername as [Server Name]
, b.database_name
, CASE WHEN COUNT(DISTINCT f.physical_device_name) IS NULL THEN 0
ELSE COUNT(DISTINCT f.physical_device_name)
END AS [#DIFFs Found]
, COUNT(DISTINCT CASE WHEN f.physical_device_name LIKE '%:\%'
OR f.physical_device_name LIKE '\\%'
THEN LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length)
ELSE '1' END) AS [Distinct DIFF locations. If > 1, check the chain!]
, Count(DISTINCT CASE WHEN f.physical_device_name LIKE '\\%'
THEN LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length) END) as [Network Share Locations (Native)]
, Count(DISTINCT CASE WHEN f.physical_device_name LIKE '{%'
AND f.physical_device_name NOT LIKE '%:\%'
AND f.physical_device_name NOT LIKE '\\%'
OR f.physical_device_name LIKE '%{%'
THEN f.physical_device_name END) as [Third Party tool]
, Count(DISTINCT CASE WHEN f.physical_device_name LIKE '%:\%'
THEN LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length) END) as [Local Drive (Native)]
FROM msdb.dbo.backupmediafamily f
INNER JOIN msdb.dbo.backupset b ON f.media_set_id = b.media_set_id
INNER JOIN sys.databases s on b.database_name = s.name
WHERE b.type in ('I') and s.database_id > 4
AND (b.backup_start_date >=CONVERT(VARCHAR(10),dateadd(day,-@days,getdate()),120))
AND is_copy_only = @copyonly
GROUP BY b.database_name
ORDER BY COUNT(DISTINCT CASE WHEN LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length)
LIKE '{%'OR LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length) like '%-%-%'
AND LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length) NOT LIKE '%:\%'
THEN '1' ELSE LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length) END) desc
SELECT
@@Servername as [Server Name]
, b.database_name
, CASE WHEN COUNT(DISTINCT f.physical_device_name) IS NULL THEN 0
ELSE COUNT(DISTINCT f.physical_device_name)
END AS [#LOGs Found]
, COUNT(DISTINCT CASE WHEN f.physical_device_name LIKE '%:\%'
OR f.physical_device_name LIKE '\\%'
THEN LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length)
ELSE '1' END) AS [Distinct LOG locations. If > 1, check the chain!]
, Count(DISTINCT CASE WHEN f.physical_device_name LIKE '\\%'
THEN LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length) END) as [Network Share Locations (Native)]
, Count(DISTINCT CASE WHEN f.physical_device_name LIKE '{%'
AND f.physical_device_name NOT LIKE '%:\%'
AND f.physical_device_name NOT LIKE '\\%'
OR f.physical_device_name LIKE '%{%'
THEN f.physical_device_name END) as [Third Party tool]
, Count(DISTINCT CASE WHEN f.physical_device_name LIKE '%:\%'
THEN LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length) END) as [Local Drive (Native)]
FROM msdb.dbo.backupmediafamily f
INNER JOIN msdb.dbo.backupset b ON f.media_set_id = b.media_set_id
INNER JOIN sys.databases s on b.database_name = s.name
WHERE b.type in ('L') and s.database_id > 4
AND (b.backup_start_date >=CONVERT(VARCHAR(10),dateadd(day,-@days,getdate()),120))
AND is_copy_only = @copyonly
GROUP BY b.database_name
ORDER BY COUNT(DISTINCT CASE WHEN LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length)
LIKE '{%'OR LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length) like '%-%-%'
AND LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length) NOT LIKE '%:\%'
THEN '1' ELSE LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length) END) desc
SELECT DISTINCT
CASE
WHEN LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length) LIKE '%:\%'
OR LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length) LIKE '\\%'
THEN LEFT(f.physical_device_name,LEN(f.physical_device_name)- @length)
ELSE 'Third-Party GUID'
END AS [BackupLoc]
, b.database_name as [DB Name]
, b.type
Into #temp
FROM msdb.dbo.backupmediafamily f
INNER JOIN msdb.dbo.backupset b ON f.media_set_id = b.media_set_id
INNER JOIN sys.databases s on b.database_name = s.name
WHERE s.database_id > 4
AND (b.backup_start_date >=CONVERT(VARCHAR(10),dateadd(day,-@days,getdate()),120))
AND is_copy_only = @copyonly
SELECT @@servername as [Server Name]
, [DB name]
, backuploc as [Unique FULL Location]
FROM #temp
WHERE type = 'D'
order by [DB Name]
SELECT @@servername as [Server Name]
, [DB name]
, backuploc as [Unique DIFF Location]
FROM #temp
WHERE type = 'I'
order by [DB Name]
SELECT @@servername as [Server Name]
, [DB name]
, backuploc as [Unique LOG Location]
FROM #temp
WHERE type = 'L'
order by [DB Name]
drop table #temp
By default, I have the file name trimming variable @length set to 20 characters, which removes that number of characters from the end of the file path. It is set this way because that is the length of the Ola default timestamp. You can change the @copyonly bit to look for copy-only backups to see if there are any running, either to look for redundancy or to keep tabs on where and when these are happening. Lastly, @days defaults to 7, which will go back that far in the backup history. In other reports, I have It set to 45 days. Feel free to make adjustments and make it work for you!
While it could likely be a bit more pretty, it does the job. I use the script across many clients as an early warning tool for them to figure out who or what is causing extra backups to run and fix it before it is found that there are broken chains in an emergency. This has saved time in restores, as well as significant storage costs in reducing redundancy.
As always, here are a few principles to follow when developing a strong backup and restore strategy:
- Regular Testing: Periodically restore backups to verify data integrity.
- Automation: Use tools that take backups and monitor backup health automatically.
- Consistent Schedules: Avoid gaps in your backup process or record and remedy any inconsistencies.
- Isolate Backup Data: Local network backups of about two weeks are what we recommend. Longer-term storage should be OFF NETWORK. One of the first tasks of ransomware is to delete or encrypt backups so that restoring in a new environment is not possible, and so you have to pay.
- Documentation: Maintain records of backup schedules and anomalies.
Database Administration demands more than just routine backups; it requires a comprehensive strategy. The highlighted script underscores the need for consistent server audits and an understanding of the backup chain. It’s not just about having backups but ensuring they work seamlessly during a restore when needed. By combining this tool with best practices like regular testing, automation, secure data storage, and diligent documentation, we can preemptively address potential issues. Remember, it’s about proactive measures over-reactive fixes, ensuring our data remains reliable and available in any scenario.
