MSSQLTips

This post is related to this tip: How to find out how long a SQL Server backup took

http://www.mssqltips.com/tip.asp?tip=1747

OK, now I'm worried. I've been having trouble with some transaction logs growing extremely large and have been working to control that when I saw this tip. So I ran the query and learned that one of my smaller databases (300 MB) took the longest 1 min 23 sec to backup while my largest database (14+ GB) took only 1 min 8 sec. So I looked at the underlying table and found the column [backup_size] and included that in Mr. LaRock's query. And boy was I surprised to learn that the backup size for my 14+ GB database is only 6656. I'm guessing that's bytes because some values from much earlier in this database's life are reasonable. The reasonable numbers occur when the [is_snapshot] value is 0. What am I seeing with these low sizes?

Just curious why you chose to use "Cast(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600" instead of "DATEDIFF(h, bup.backup_start_date, bup.backup_finish_date)". Or you could have used a CTE to only perform the DateDiff once and then performed the calculations on the calculated seconds.

 Also, DateDiff returns an int value. You don't need to cast it as int.

 Otherwise, good information!!

Hi,

I use something very similar, except that it shoots a HTML formatted email twice a day listing the Last FullBackup and subsequent Transaction Logs. It is published on SQL Server Central. Please do check it out http://www.sqlservercentral.com/scripts/sp_send_dbmail/66701/

The reason I am posting this comment is that not only is the time taken important but also the fact that the backup did happen. Listing a Full Backup + Subsequent transaction logs means your database restoration path is visible to you and you know if you are in trouble or not if a backup was missed.

Its great to see a like minded coder online.

Thanks
Pratap

 

The minutes are not correct when the number of seconds is greater than or equal to 3600.  You need another modulo 60.

+ CAST(((CAST(DATEDIFF(ss, bup.backup_start_date, bup.backup_finish_date) AS int))/60)%60 AS varchar)+ ' minutes, '

What about the opposite. How long did a recovery take?

 

Thanks

Tom Groszko

Yes, I am interested in the time and date of a restore and how long it took (duration of restore).  I have not been able to find any system table or view that would provide this information.  The article posted on mssqltips about obtaining the last time a restore was done does shown when a restore started but not when it ended and no restore duration.  We do quarterly restore testing and must document this information to satisfy Auditors.  The only way I have been able to determine the completion time is looking at the SQL logs not from any dictionary tables or views.  You would think that Microsoft would have recorded this type of information somewhere.