Sql-server – Why is user: system doing an hourly backup of the SQL databases

sql server

I've got a pretty standard maintenance plan set up for my SQL Server databases; yet in addition to that plan, all of my databases, user and system, are backed up every hour by user: System… where is that getting initiated from and how can I turn it off? There is also an associated MSMQ backup of components: registry, sysq, https, etc during the same time frame.

Best Answer

Your System Admin probably has a program such as BackupExec which backs up everything organisation-wide. Check with them.

Typically, you'll see the backup device listed as a unique identifier, if some other system is managing backups.

I occasionally use this query to check backup history details:

DECLARE @DBName SYSNAME;
SET @DBName = DB_NAME(); -- modify these as you desire.
SET @DBName = NULL; -- comment this line if you want to limit the history
--SET @DBName = '<some database name>';
;WITH src AS 
(
SELECT DatabaseName = bs.database_name
    , BackupStartDate = bs.backup_start_date
    , CompressedBackupSizeMB = bs.compressed_backup_size / 1048576
    , ExpirationDate = bs.expiration_date
    , BackupSetName = bs.name
    , RecoveryModel = bs.recovery_model
    , ServerName = bs.server_name
    , BackupType = CASE bs.type 
          WHEN 'D' THEN 'Database' 
          WHEN 'L' THEN 'Log' 
          ELSE '[unknown]' END
    , LogicalDeviceName = bmf.logical_device_name
    , PhysicalDeviceName = bmf.physical_device_name
    , rn = ROW_NUMBER() OVER (PARTITION BY bs.database_name 
            ORDER BY bs.backup_start_date DESC)
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf 
        ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE (bs.database_name = @DBName
    OR @DBName IS NULL)
    AND bs.backup_finish_date >= DATEADD(DAY, -7, GETDATE())
)
SELECT src.DatabaseName
    , src.BackupStartDate
    , CompressedBackupSizeMB = CONVERT(INT, src.CompressedBackupSizeMB)
    , src.ExpirationDate
    , src.BackupSetName
    , src.RecoveryModel
    , src.ServerName
    , src.BackupType
    , src.LogicalDeviceName
    , src.PhysicalDeviceName
FROM src
WHERE src.rn = 1
     AND src.DatabaseName NOT IN (
        'master'
        , 'model'
        , 'msdb'
        , 'tempdb'
     );

That should return a single row for each database backed up over the last 7 days. You can limit this to a single database by modifying the @DBName variable.

Related Question