Friday, March 22, 2019

How long is that SQL Server backup going to take?

Good question.  I was backing up a customer database today and let them know about how long to expect it to take.  They said 'No... it should only take a couple hours!'  I had no experience w/this particular database before, so I thought I'd query the system to see how long the last one ran for.  This post is the query I used.  Just a super quick way to get the duration of the last backup for the given database.  


DECLARE @dbname sysname
SET @dbname = 'JDE_PRODUCTION' -- your dbname here
SELECT
  bup.user_name ByWhom,
  bup.database_name DatabaseName,
  bup.server_name ServerName,
  bup.backup_start_date StartTime,
  bup.backup_finish_date EndTime,
  CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS INT))/3600 AS VARCHAR) + ' hours, '
       + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS INT))/60 AS VARCHAR)+ ' minutes, '
       + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS INT))%60 AS VARCHAR)+ ' seconds' Duration
FROM msdb.dbo.backupset bup
WHERE bup.backup_set_id IN (
   SELECT MAX(backup_set_id)
   FROM msdb.dbo.backupset
   WHERE database_name = @dbname
   AND type = 'D' -- change for the type you need;  I = DIFF, L = LOG, D = FULL
   GROUP BY database_name
   );

And there you have it.  Short and sweet.  Just the kind of tSQL I favor.  :)   






It's not a guaranteed duration going forward, but it will certainly give you very close expectations.  Unless of course the thing increased or decreased in size dramatically... but that's another story.

No comments:

Post a Comment