SQL-Server: 是否有 SQL 脚本可以用来确定 SQLServer 备份或恢复过程的进度？

Use STATS option: http://msdn.microsoft.com/en-us/library/ms186865.aspx

I found this sample script here that seems to be working pretty well:

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(10,2), r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min]
, CONVERT(NUMERIC(10,2), r.estimated_completion_time/1000.0/60.0) AS [ETA Min]
, CONVERT(NUMERIC(10,2), r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours]
, CONVERT(VARCHAR(1000),
(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)
)
) AS [SQL]
FROM sys.dm_exec_requests r
WHERE command IN ('RESTORE DATABASE', 'BACKUP DATABASE')

Use STATS in the BACKUP command if it is just a script.

Inside code it is a bit more complicated. In ODBC for example, you set SQL_ATTR_ASYNC_ENABLE and then look for SQL_STILL_EXECUTING return code, and do some repeated calls of SQLExecDirect until you get a SQL_SUCCESS (or eqiv).

Yes. If you have installed sp_who2k5 into your master database, you can simply run:

sp_who2k5 1,1

The resultset will include all the active transactions. The currently running backup(s) will contain the string "BACKUP" in the requestCommand field. The aptly named percentComplete field will give you the progress of the backup.

Note: sp_who2k5 should be a part of everyone's toolkit, it does a lot more than just this.

If you know the sessionID then you can use the following:

SELECT * FROM sys.dm_exec_requests WHERE session_id = 62

Or if you want to narrow it down:

SELECT command, percent_complete, start_time FROM sys.dm_exec_requests WHERE session_id = 62

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

To monitor the backup or restore progress completely separate from the session where the backup or restore was initiated. No third party tools required. Tested on Microsoft SQL Server 2012.

SELECT percent_complete, *
FROM sys.dm_exec_requests
WHERE command In ( 'RESTORE DATABASE', 'BACKUP DATABASE' )

Here's a simple script that generally does the trick for me:

SELECT command, percent_complete,total_elapsed_time, estimated_completion_time, start_time
FROM sys.dm_exec_requests
WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

I think the best way to find out how your restore or backup progress is by the following query:

USE[master]
GO
SELECT session_id AS SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')
GO

The query above, identify the session by itself and perform a percentage progress every time you press F5 or Execute button on SSMS!

The query was performed by the guy who write this post

I am using sp_whoisactive, very informative an basically industry standard. it returns percent complete as well.

simply run bkp_status on master db you will get backup status

Script to check the Backup and Restore progress in SQL Server:

Many times it happens that your backup (or restore) activity has been started by another Database Administrator or by a job, and you cannot use the GUI anything else to check the progress of that Backup / Restore.

By combining multiple commands, I have generated below script which can give us a summary of current backups and restores which are happening on the server.

select
r.session_id,
r.blocking_session_id,
db_name(database_id) as [DatabaseName],
r.command,
[SQL_QUERY_TEXT] = Substring(Query.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN - 1
THEN Datalength(Query.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1),
[SP_Name] =Coalesce(Quotename(Db_name(Query.dbid)) + N'.' + Quotename(Object_schema_name(Query.objectid, Query.dbid)) + N'.' +
Quotename(Object_name(Query.objectid, Query.dbid)), ''),
r.percent_complete,
start_time,
CONVERT(VARCHAR(20), DATEADD(ms, [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 [Remaning_ETA_MIN],
CONVERT(NUMERIC(6, 2), r.[estimated_completion_time] / 1000.0 / 60.0/ 60.0) AS [ETA_Hours],
wait_type,
wait_time/1000 as Wait_Time_Sec,
wait_resource
from sys.dm_exec_requests r
cross apply sys.fn_get_sql(r.sql_handle) as Query where r.session_id>50 and command IN ('RESTORE DATABASE','BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

Try wih :

SELECT * FROM sys.dm_exec_requests where command like '%BACKUP%'


SELECT command, percent_complete, start_time FROM sys.dm_exec_requests where command like '%BACKUP%'


SELECT command, percent_complete,total_elapsed_time, estimated_completion_time, start_time
FROM sys.dm_exec_requests
WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

Add STATS=10 or STATS=1 in backup command.

BACKUP DATABASE [xxxxxx] TO  DISK = N'E:\\Bachup_DB.bak' WITH NOFORMAT, NOINIT,
NAME = N'xxxx-Complète Base de données Sauvegarde', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO.

SELECT  session_id as SPID, command, start_time, percent_complete,
dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time,
a.text AS Query
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE', 'BACKUP LOG', 'RESTORE DATABASE', 'RESTORE LOG')

For anyone running SQL Server on RDS (AWS), there's a built-in procedure callable in the msdb database which provides comprehensive information for all backup and restore tasks:

exec msdb.dbo.rds_task_status;

This will give a full rundown of each task, its configuration, details about execution (such as completed percentage and total duration), and a task_info column which is immensely helpful when trying to figure out what's wrong with a backup or restore.

I had a similar issue when working on Database restore operation on MS SQL Server 2012.

However, for my own scenario, I just needed to see the progress of the DATABASE RESTORE operation in the script window

All I had to do was add the STATS option to the script:

USE master;
GO


ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
    

RESTORE DATABASE mydb
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\my_db_21-08-2020.bak'
WITH REPLACE,
STATS = 10,
RESTART,
MOVE 'my_db' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\my_db.mdf',
MOVE 'my_db_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mydb_log.ldf'
GO
    

ALTER DATABASE mydb SET MULTI_USER;
GO

And then I switched to the Messages tab of the Script window to see the progress of the DATABASE RESTORE operation:

If you want to get more information after the DATABASE RESTORE operation you can use this command suggested by eythort:

SELECT command, percent_complete, start_time FROM sys.dm_exec_requests where command = 'RESTORE DATABASE'

That's all.

I hope this helps