SQL Server 2012 database backup successfully report but no backup file

Question

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

• DOK: Are you quite sure you are looking for the file in the right folder?
• Tabriz Atayi: I am quite sure. Even I changed folder for test
• Aaron Bertrand: 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.

Answer 1

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. 
backupset.type 
D --> FULL
I --> DIff or incrimental 
L --> Log backups
*/
DECLARE @dbname sysname
SET @dbname = ''
SELECT 
 @@servername [ServerName]
 ,master.sys.sysdatabases.name [DatabaseName]
 ,msdb.dbo.backupset.backup_start_date [Backup Date]
 ,msdb.dbo.backupset.user_name
 ,datediff(second, msdb.dbo.backupset.backup_start_date,
 msdb.dbo.backupset.backup_finish_date) [Duration-seconds]
 ,msdb.dbo.backupmediafamily.physical_device_name [File Location] 
 ,msdb.dbo.backupset.type
FROM
 msdb.dbo.backupmediafamily,
 master.sys.sysdatabases
 LEFT OUTER JOIN
 msdb.dbo.backupset 
 ON master.sys.sysdatabases.name = msdb.dbo.backupset.database_name 
WHERE 
 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 + '%'
ORDER BY 
 master.sys.sysdatabases.name
 ,msdb.dbo.backupset.backup_start_date 
 ,msdb.dbo.backupset.backup_finish_date
 ,msdb.dbo.backupmediafamily.physical_device_name
 ,msdb.dbo.backupset.type

Answer 2

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?

Answer 3

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

Answer 4

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.

Answer 5

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