enter image description here

I have created maintenance plan in SQL Server 2012. And every day, the maintenance plan should backup the database. There is no database backup file when I look in the folder where the backups must be stored. But SQL Server logs history about this maintenance plan are successful.

What is the problem?

I am using SQL Server 2012. The operating system is Windows Server 2008 R2.

Thank you for paying attention.

enter image description here

  • Are you quite sure you are looking for the file in the right folder?
    – DOK
    Jan 11, 2013 at 13:13
  • I am quite sure. Even I changed folder for test
    – Tabriz Atayi
    Jan 11, 2013 at 13:16
  • 4
    Also just because SQL Server successfully put a backup file in a folder doesn't mean that someone (or something) else didn't delete it or move it. Jan 11, 2013 at 17:08

5 Answers 5


The msdb database keeps history of where, when, who, how big etc. of backups. I have often needed to know where a backup went or who did it or when was the last backup.

This is set to return the last two days of backup history. You can put a specific database in the value, if you leave it blank it will return for all databases. This query works for 2005+. Just tested in 2012.

Find Where DB Backups Went Physical Location
For last two days.  
D --> FULL
I --> DIff or incrimental 
L --> Log backups

DECLARE @dbname     sysname
SET @dbname = ''

     @@servername [ServerName]
    ,master.sys.sysdatabases.name [DatabaseName]
    ,msdb.dbo.backupset.backup_start_date [Backup Date]
    ,datediff(second, msdb.dbo.backupset.backup_start_date,
    msdb.dbo.backupset.backup_finish_date) [Duration-seconds]
    ,msdb.dbo.backupmediafamily.physical_device_name [File Location]    
    ON master.sys.sysdatabases.name = msdb.dbo.backupset.database_name  
    msdb.dbo.backupset.type in( 'D', 'I', 'L')
AND msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
and msdb.dbo.backupset.backup_start_date > getdate() - 2
AND master.sys.sysdatabases.name not in ('pubs','northwind', 'tempdb','adventureworks')
AND master.sys.sysdatabases.name like '%' + @dbname + '%'

Look at the duration of the job, it's zero seconds. The job is completing successfully but the job must be configured incorrectly. It won't actually be doing any backups in milliseconds, so look at the package to make sure it's set up properly.

Have you checked the right database? And is this a secondary node in an availability group as per the warning message?


Check on 'For availability databases, ignore replica .........' ... this worked for me enter image description here


Just a guess that you are looking at the folder on a different computer. Your backup location represents folder where SQL Server instance is running.


This happened to me today. You should check your servers to see if they are up and running. The secondary is surely down.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.