Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Are you quite sure you are looking for the file in the right folder? –  DOK Jan 11 '13 at 13:13
    
I am quite sure. Even I changed folder for test –  Tabriz Atayi Jan 11 '13 at 13:16
    
Can you show the details of the maintenance plan, please? Some screenshots or something would help us assist you better. –  SchmitzIT Jan 11 '13 at 13:22
2  
Just in case; the path in the plan is on the server, not the machine that executes it –  Alex K. Jan 11 '13 at 13:26
2  
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. –  Aaron Bertrand Jan 11 '13 at 17:08
show 4 more comments

migrated from stackoverflow.com Jan 11 '13 at 16:23

This question came from our site for professional and enthusiast programmers.

2 Answers

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
share|improve this answer
add comment

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?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.