Where is the BAK file?

SQL Server Administration
1

Hi experts.
I know the db is getting a full backup daily but I can't find the .bak file.
Normally I would look at the backup job but the agent node will not open in SSMS. I have checked the default location in instance -> Properties.
The .bak file is not in that path.
Are there any other options to find the location of the bak file?
Thanks

2 
SELECT b.database_name, b.type, b.backup_finish_date, f.physical_device_name
FROM msdb.dbo.backupset b
INNER JOIN msdb.dbo.backupmediafamily f on b.media_set_id=f.media_set_id
WHERE b.database_name='myDatabase'

It's a variation on the example found here:

You'd have to tweak it further for date ranges, other columns, etc. Also make sure there isn't a job scheduled to clear the backup history, at least not too frequently.

1 Like
3

Thank you, @robert_volk . The query returns:
physical_device_name
{A8F79623-0E38-4F6C-B05F-45E457295305}3

This looks like a backup device but there are no backup devices shown in SSMS.
Any way to translate the above to an actual path?

4

That is a backup done by the VSS Writer which was probably called by your server backup software. If that is the only backup showing, you probably need to speak to your infrastructure people.

5

I added 2 columns that might give you more insight, most 3rd party backup software that use VSS populate the name and/or description when they make a SQL Server backup. For instance, Netbackup will put something in there to identify them.

6

Hi There
I created a batch file called mysqlbackup.bat

Within the batch files I had the following
Items between # # are your details

set day=%date:~0,2%
set month=%date:~3,2%
set year=%date:~6,4%
set hour=%time:~0,2%
set min=%time:~3,2%
set sec=%date:~6,2%

set l=%day%%month%%year%
set m=%hour%%min%%sec%

sqlcmd -S #Name of your PC#\SQLEXPRESS -Q "BACKUP DATABASE TO DISK = N'#location you want to back up to#\#Datbasename#%l%%m%.bak' WITH INIT, NAME = N'Automatic back up of database', STATS = 1"

Pause

1 Like
7

Thanks fogmaster!

8

Thank you for solution!
I am also having this problem.