SQL Server 2012 database backup successfully report but no backup file

Question

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.

Answer 1

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 2

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