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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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,

   distinct CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
 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.physical_device_name, AS backupset_name
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'

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

share|improve this question
What time zone is your server in? How about your workstation where you are reading these results? Any chance any times are in UTC, or another time zone? I know that in some parts of the UI SSMS is inconsistent about deciding whether to show client time or server time... – Aaron Bertrand Jan 24 '14 at 18:51
Time zone is Eastern time. I am reading these results from my workstation and the server. – iamZel Jan 24 '14 at 19:00
If you SELECT getdate() does it give you the date/time you expect? – Kenneth Fisher Jan 24 '14 at 19:19
Yes, I get the correct date/time – iamZel Jan 24 '14 at 19:21

Your Answer


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

Browse other questions tagged or ask your own question.