ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 计算SQL Server备份一次所花的时间

计算SQL Server备份一次所花的时间

原创 Linux操作系统 作者:iSQlServer 时间:2009-05-19 16:03:02 0 删除 编辑

  在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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-10-17

  • 博文量
    1319
  • 访问量
    2084306