計算SQL Server備份一次所花的時間

　　在msdb資料庫內這些資料是現成的，從而使這個解決方案和幾行T-SQL程式碼一樣簡單。

　　下面提供的T-SQL允許你輸入想要的資料庫名稱。我也新增了一行來過濾結果，從而限制你只能看到當前master.dbo.sysdatabases表中列出的資料庫。如果你註釋掉這行程式碼，你將在資料庫的最後一次備份例項時所返回的資訊，不管這個資料庫當前是否列在master.dbo.sysdatabases表中。

　　返回詳細資訊

　　這是上面提到的T-SQL程式碼 ：

　　DECLARE @dbname sysname

　　SET @dbname = NULL --set this to be whatever dbname you want

　　SELECT bup.user_name AS [User],

　　bup.database_name AS [Database],

　　bup.server_name AS [Server],

　　bup.backup_start_date AS [Backup Started],

　　bup.backup_finish_date AS [Backup Finished]

　　,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'

　　AS [Total Time]

　　FROM msdb.dbo.backupset bup

　　WHERE bup.backup_set_id IN

　　(SELECT MAX(backup_set_id) FROM msdb.dbo.backupset

　　WHERE database_name = ISNULL(@dbname, database_name) --if no dbname, then return all

　　AND type = 'D' --only interested in the time of last full backup

　　GROUP BY database_name)

　　/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */

　　AND bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)

　　ORDER BY bup.database_name

　　這些指令碼將返回下面的結果集：

　　影像

　　這是上面指令碼返回的示例結果集的截圖：　

　　如果你想得到所有備份的一個列表，而不只是最近的備份列表，那麼你可以執行下面的程式碼：

　　DECLARE @dbname sysname

　　SET @dbname = NULL --set this to be whatever dbname you want

　　SELECT bup.user_name AS [User],

　　bup.database_name AS [Database],

　　bup.server_name AS [Server],

　　bup.backup_start_date AS [Backup Started],

　　bup.backup_finish_date AS [Backup Finished]

　　,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'

　　AS [Total Time]

　　FROM msdb.dbo.backupset bup

　　/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */

　　WHERE bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)

　　ORDER BY bup.database_name

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-600460/，如需轉載，請註明出處，否則將追究法律責任。