asked on

msdb.dbo.backupset for backup location

Is there any easy way to identify where backup jobs are writing the backup file too? I dont currently have access to an MSSQL instance, but wondered if msdb.dbo.backupset may give any pointers? Does anyone have a query? To show last backup per database - where it was written to?

Pau Lo

ASKER

I also wondered if using the BACKUP command if theres a default location it will write the backup too, unless you specific elsewhere?

ASKER CERTIFIED SOLUTION

D B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D B

If all you want is the most recent backup, try this. See http://technet.microsoft.com/en-us/library/ms190284.aspx for information on device_type:

SELECT	x.database_name AS [DB Name],
		x.physical_device_name AS [Filename],
		x.backup_start_date AS [Backup Date]
FROM	(
		  SELECT	bs.database_name,
					bs.backup_start_date,
					bmf.physical_device_name,
					device_type,
					ROW_NUMBER() OVER ( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC ) AS Ranking
		  FROM		msdb.dbo.backupmediafamily bmf
		  INNER JOIN msdb.dbo.backupmediaset bms
		  ON		bmf.media_set_id = bms.media_set_id
		  INNER JOIN msdb.dbo.backupset bs
		  ON		bms.media_set_id = bs.media_set_id
		  WHERE		bs.[type] = 'D'
--                  AND bs.is_copy_only = 0 
		 ) x
WHERE	x.Ranking = 1
		AND device_type = 2
ORDER BY x.database_name;

Open in new window

D B

The default backup location is a registry entry. There are a few ways to get it (other than just using RegEdit and browsing to the key.

This will do it in T-SQL if permissions are set:

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

Open in new window

This will give you the value using PowerShell:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST" 
$s.Settings.BackupDirectory

Open in new window

Pau Lo

ASKER

Thanks, a bit lost though, which field shows where the backup is written to?

D B

physical_device_name