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?
LVL 4
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pma111Author Commented:
I also wondered if using the BACKUP command if theres a default location it will write the backup too, unless you specific elsewhere?
0
dbbishopCommented:
This will give you some basic information. A screenshot of part of the data is shown. device_type = 2 is a physical file:
SELECT	database_name,
		server_name,
		machine_name,
		physical_device_name,
		device_type,
		backup_start_date,
		backup_finish_date,
		backup_size
FROM [msdb].[dbo].[backupmediaset] bms
INNER JOIN [msdb].[dbo].[backupmediafamily] bmf
ON bmf.media_set_id = bms.media_set_id
INNER JOIN [msdb].dbo.backupset bs
ON bs.media_set_id = bms.media_set_id
WHERE	device_type = 2

Open in new window

backupdata.png
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbbishopCommented:
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

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dbbishopCommented:
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

0
pma111Author Commented:
Thanks, a bit lost though, which field shows where the backup is written to?
0
dbbishopCommented:
physical_device_name
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.