A client of mine setup backups on their server and the jobs are running successfully, but the backups are not showing up in the backup location. Finding the location of any backup commands can be found using a simple sql statement.
Here is a screenshot of the Agent job history, showing that the transaction log backup completed successfully.
But when I look in the directory, the transaction log backups are not there.
I then run this script to show me where the files are.
SELECT physical_device_name,
backup_start_date,
backup_finish_date,
backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'cityworkstest' and physical_device_name like '%gisbkup%'
ORDER BY backup_finish_date DESCYou just need to change the “database_name” parameter for the database you want to search for.
Looking at the Physical_device_name column below, I can see that the backup is going into the root drive location instead of the database named directory.
How did this happen? I do not know, so I open up the maintenance plan and look at the settings. I see that the “create a sub-directory for each database” is not selected, so the backup just goes to the root folder instead of a database named directory.
After modifying that field, and then rerunning the job, we can see that now the transaction logs are going to the database named folder.
Looking in the directory shows the files are now going to the correct location.
There is plenty of information to be found querying the internal DMV’s. To read more about the information in the backupmediafamily or backupset tables, please follow the links below.
or
Jim Salasek's SQL Server Blog 