Guoy

遥看成梦,近看成风

当前SQL Server 数据备份要花多少时间?

SQL Server 数据库备份要多久?

当用户执行数据备份时,如何动态地了解当前备份还要花的时间?

数据库备份时间取决于硬件的配置和性能,如下所示:
①IO - Disks -  IO的性能;
②CPU – CPU的数量;
③Network – 是否备份是异地的;
④Memory等。

如果用户想了解多少百分比的备份已完成,有两种方法:

方法1,用户可以在backup命令中加入 Stats变量STATS [ = percentage ]:

BACKUP DATABASE  to disk = 'M:\DBNAME_DDMMYY.BAK' WITH STATS = 10

已10%的递增显示备份进度,执行效果如下:
10 percent of database backup completed
20 percent of database backup completed
30 percent of database backup completed
…….


方法2,利用sys.dm_exec_request 来查看预期的备份执行情况,具体查询语句如下:

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [PERCENT Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GETDATE()),20) AS [ETA COMPLETION TIME],
CONVERT(NUMERIC(6,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed MIN],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0) AS [ETA MIN],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(100),(SELECT SUBSTRING(TEXT,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

注:只有SQL Server 2005和以上的版本才支持此方法。

相关文档:http://msdn.microsoft.com/en-us/library/aa225964(v=SQL.80).aspx

转载自:http://social.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/e6e438a0-667d-4768-be72-e7f760cd07f7

聚划算