Backup file paths

Question

Backup file paths

lindss01

SSCrazy

Points: 2264
More actions
October 6, 2004 at 11:11 am

#109659

I am looking for the last backup file path. Is it in the database somewhere. Can I access it via T-SQL?

Thanks
Steve

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Use Enterprise Manager, drill down to Management, expand that. Right click on Backup. That should show you where your backups are designed to go to.

What version are you using and how was the backup done?

-SQLBill

lindss01 SSCrazy Points: 2264 More actions · Answer 2

Thanks for the input. I am trying to create a stored procedure to restore a database under a new name using the last backup. I need to access that information from a SELECT statement within a stored procedure

Steve

nlion84DBA Mr or Mrs. 500 Points: 562 More actions · Answer 3

Hello

If you backed up your database to disk like :

backup database northwind to disk = 'nwbackup.bak'

Sql Server creates a folder: Program Files\Microsoft SQL Server\MSSQL\Backup\

and places the files there.

lindss01 SSCrazy Points: 2264 More actions · Answer 4

All are backups are done via maintenace plans and the files have the format of

<dbname><yearmonthday><hourminutesecond>.BAK.

I am using sp_start_job to use the maintenance plan backup.

Steve

Ray Mond SSCertifiable Points: 7159 More actions · Answer 5

You might want to take a look at the msdb..backupfile table.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

lindss01 SSCrazy Points: 2264 More actions · Answer 6

Thanks Peter, but it only has the .mdf and .ldf files not the location of the .BAK.

Still looking,

Steve

Peter Tillotson Hall of Fame Points: 3325 More actions · Answer 7

I restore my last backup onto a warm start server and use the following SQL to find the path of the last full backup file. Because I am restoring onto another server I save the backup to a shared network folder. The SQL returns the folder name but not the server name (as it is runnning on that server) so I append the server name to the file path returned:

declare @file_name varchar(255)

select top 1 @file_name = '\\Servername' + substring(physical_device_name,

charindex('\', physical_device_name), 4000) from msdb.dbo.BackupMediaFamily bmf

left join msdb.dbo.BackupSet bs on bs.media_set_id = bmf.media_set_id

WHERE bs.database_name = 'Databasename' and type = 'D'

ORDER BY bs.backup_start_date desc

select @file_name

Change the database name and change 'D' to 'L' if you want to find the last transaction log backup.

Regards

Peter

rgrios Old Hand Points: 364 More actions · Answer 8

Try this

use msdb

go

SELECT backupset.database_name, backupmediafamily.physical_device_name

FROM backupmediafamily INNER JOIN

backupset ON backupmediafamily.media_set_id = backupset.media_set_id

WHERE (backupset.backup_finish_date > '10/06/2004')

nlion84DBA Mr or Mrs. 500 Points: 562 More actions · Answer 9

I suppose if you have the name and path some files you can use the Restore Headeronly command to verify the files are what you want. See BOL for more info

Ray Mond SSCertifiable Points: 7159 More actions · Answer 10

Caught me napping there! As others have pointed out, msdb..backupmediafamily is the table to look at.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

lindss01 SSCrazy Points: 2264 More actions · Answer 11

Here is my final SQL query that I used to get this information.

DECLARE @olddatabasename as sysname,@backuppath as NVARCHAR(4000)

SELECT @backuppath = MAX([physical_device_name]) FROM [msdb].[dbo].[backupmediafamily]

WHERE [physical_device_name] like '%' + @olddatabasename + '%' AND

[physical_device_name] like '%.BAK'

Thanks for all the input

Steve