17 May 2018

Full backup to odd path

by Eelco Drost

5,723 0 Compliance

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

This custom metric queries the MSDB database to search for multiple paths for a full backup

Metric definition

Name

Full backup to odd path

Description

This custom metric queries the MSDB database to search for multiple paths for a full backup

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			CREATE TABLE #defaultPath (
	value VARCHAR(500)
	,BackupPath VARCHAR(250)
	)

INSERT INTO #defaultPath
EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
	,N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'
	,N'BackupDirectory'

SELECT COUNT(*)
FROM (
	SELECT LEFT(b.physical_device_name, LEN(b.physical_device_name) - charindex('\', reverse(b.physical_device_name), 1) + 1) AS [Path]
	FROM msdb.dbo.backupset a
	INNER JOIN msdb.dbo.backupmediafamily b ON a.media_set_id = b.media_set_id
	WHERE a.type = 'D'
		AND a.database_name = DB_NAME(DB_ID())
		AND a.backup_finish_date BETWEEN DATEADD(MINUTE, - 2, GETDATE())
			AND GETDATE()
		AND LEFT(b.physical_device_name, LEN(b.physical_device_name) - charindex('\', reverse(b.physical_device_name), 1) + 1) <> (
			SELECT BackupPath
			FROM #defaultPath
			)
	GROUP BY LEFT(b.physical_device_name, LEN(b.physical_device_name) - charindex('\', reverse(b.physical_device_name), 1) + 1)
	) t

DROP TABLE #defaultPath
			
				
					
				
					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
				
						CREATE TABLE #defaultPath (
	value VARCHAR(500)
	,BackupPath VARCHAR(250)
	)
 
INSERT INTO #defaultPath
EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
	,N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'
	,N'BackupDirectory'
 
SELECT COUNT(*)
FROM (
	SELECT LEFT(b.physical_device_name, LEN(b.physical_device_name) - charindex('\', reverse(b.physical_device_name), 1) + 1) AS [Path]
	FROM msdb.dbo.backupset a
	INNER JOIN msdb.dbo.backupmediafamily b ON a.media_set_id = b.media_set_id
	WHERE a.type = 'D'
		AND a.database_name = DB_NAME(DB_ID())
		AND a.backup_finish_date BETWEEN DATEADD(MINUTE, - 2, GETDATE())
			AND GETDATE()
		AND LEFT(b.physical_device_name, LEN(b.physical_device_name) - charindex('\', reverse(b.physical_device_name), 1) + 1) <> (
			SELECT BackupPath
			FROM #defaultPath
			)
	GROUP BY LEFT(b.physical_device_name, LEN(b.physical_device_name) - charindex('\', reverse(b.physical_device_name), 1) + 1)
	) t
 
DROP TABLE #defaultPath

Instances to collect from

Select all

Databases to collect from

All

Collection frequency

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

You could add an alert once the baseline for your environment has been established.

5,723 Rate

Click to rate this post!

[Total: 3 Average: 3.7]

Full backup to odd path

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics