none
Can't find how backups are being created RRS feed

  • Question

  • I have set up a job to backup a database daily. I was surprised recently to find that backups are being done every 2 hours for the same database. These are listed in event viewer (source: MSSQLSERVER, user: N/A) along with the expected backups. They are also listed in SQL Server logs (Source: BACKUP). However there is no associated SQL Server Agent job and there are no maintenance plans. Queries against various system views turn up nothing but the expected job. I'm using SQL Server Manager as sa.

    How do I find and modify this backup?

     

    Monday, January 6, 2014 6:07 PM

Answers

  • Thanks for replies. This was being done by some code running on the server that was not on the task scheduler. 

    In short, there is a web application that runs this backup at more or less random intervals when the worker process is restarted. I've commented this out in the code and recompiled. 

    Was a very unusual circumstance. 

    • Edited by Rich Frenkel Tuesday, January 7, 2014 2:06 PM
    • Marked as answer by Rich Frenkel Tuesday, January 14, 2014 1:07 PM
    Monday, January 6, 2014 6:38 PM

All replies

  • Hello,

    It sounds like your job has a schedule that is set for every 2 hours. Check the schedules attached to the job to make sure (note this is not part of the maintenance plan, jobs, schedules, and maintenance plans are all separate entities).


    Sean Gallardy | Blog | Twitter

    Monday, January 6, 2014 6:16 PM
    Answerer
  • Try running this to see where the backups are being backuped up to, what server the request came from and what account executed the command. This may be a case where the backup command is being run remotely if you can't find it anywhere on the local server.

    USE [msdb]
    GO 
    SELECT 
    	[bs].[database_name], 
    	[bs].[backup_start_date], 
    	[bs].[backup_finish_date], 
    	[bs].Server_name,
    	[bs].user_name AS [BackupCreator] ,
    	[bmf].physical_device_name
    FROM msdb..backupset bs  
    INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] 
    ORDER BY [bs].[backup_start_date] DESC


    Andre Porter



    • Edited by Andre Porter Monday, January 6, 2014 6:36 PM
    • Proposed as answer by Andre Porter Monday, January 6, 2014 8:35 PM
    Monday, January 6, 2014 6:32 PM
  • Thanks for replies. This was being done by some code running on the server that was not on the task scheduler. 

    In short, there is a web application that runs this backup at more or less random intervals when the worker process is restarted. I've commented this out in the code and recompiled. 

    Was a very unusual circumstance. 

    • Edited by Rich Frenkel Tuesday, January 7, 2014 2:06 PM
    • Marked as answer by Rich Frenkel Tuesday, January 14, 2014 1:07 PM
    Monday, January 6, 2014 6:38 PM
  • Check for the backup history and play close attention to the physical_device_name column. This could be a backup taken by an enterprise backup software, usually indicated by a name with GUID values. If it is, coordinate with your systems team to make sure that everyone's on the same page as far as recovery objectives and service level agreements are concerned

    SELECT database_name,Backup_type=   
         CASE  type             
        when 'D' then 'FULL'   
        when 'I' then 'DIFFERENTIAL'   
        when 'L' then 'LOG'   
        when 'F' then 'FILE/FILEGROUP'   
        when 'G' then 'DIFFERENTIAL FILE'   
        when 'P' then 'PARTIAL'    
        when 'Q' then 'DIFFERENTIAL PARTIAL'   
        END                
          , physical_device_name,backup_start_date            
                  
      FROM msdb.dbo.backupset a inner join msdb.dbo.backupmediafamily b   
      on a.media_set_id = b.media_set_id   
     
    order by a.database_name,a.backup_start_date DESC   


    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master
    Blog | Twitter | LinkedIn
    SQL Server High Availability and Disaster Recover Deep Dive Course


    Monday, January 6, 2014 6:44 PM
    Moderator