I have a database that is backed up via maintenance plan. The database is scheduled to be backed up at 8:00PM. It usually takes 6 hours to complete. According to this query,
SELECT distinct CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, CAST((DATEDIFF(MINUTE, msdb.dbo.backupset.backup_start_date,msdb.dbo.backupset.backup_finish_date)) AS varchar)+ ' min ' AS [Total Time] , CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END AS backup_type, Cast(msdb.dbo.backupset.backup_size/1024/1024/1024/1024 AS numeric(10,2)) AS 'Backup Size(TB)', --msdb.dbo.backupmediafamily.logical_device_name, --msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name --msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id and database_name = 'database1' and msdb.dbo.backupset.backup_start_date>'2014-1-22' and msdb.dbo.backupset.backup_start_date<'2014-01-24 23:59:59' ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date
the database doesn't start at 8:00PM. It starts the next day at an early time such as 12:03AM. If the backup completes before 12:00AM, the query shows that the backup was started at 8:00PM.
Scenario 1: Backup begins at 1/5/14 at 8pm. Backup completes at 1/6/14 at 2AM. This query displays that the backup began at 1/16/14 at 12:05AM and completed at 1/16/14 at 2AM.
Scenario 2: Backup begins at 1/6/14 at 8pm. Backup completes at 1/6/14 at 11pm. THe query displays that the backup began at 1/6/14 at 8pm and finished at 1/6/14 at 11pm.
If I look at the maintenance plan history logs, it shows that the database is always backed up at 8:00PM.
Any ideas to why I get these results?
I also forgot to mention that commvault is used to back up the databases in conjunction with the sql server backups. Why is that? I have yet to figure out. There should be either sql server backups or commvault backups. I don't know if that affect the results somehow. I thought it was something to mention