Finding the restore progress of SQL server from inside c#

Question

I know this is most-likely a simple question but when you restore a database from inside SQL management studio you can set the update interval with stats

RESTORE DATABASE [test] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\test.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10

If I wanted to execute that line of code from inside c# how would i get the progress? Currently I just use System.Data.SqlClient.SqlCommand.ExecuteNonQuery() but I can not figure out how to get the progress.

Also, if it is any faster, using the Microsoft.SQLServer namespace is acceptable.

Answer 1

ExecuteNonQuery is only going to return once the operation is complete. There might be a way to monitor its progress from a connection on another thread or to use an async call, but you could also look at using SMO, which provides a way to register callbacks see http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.restore.aspx and http://msdn.microsoft.com/en-us/library/ms162133.aspx with PercentComplete event

Answer 2

Before you start the operation get the connection session id:

SELECT @@SPID;

Then start your backup request. From a different connection, query sys.dm_exec_requests and look at percent_complete for the session that executes the restore statement:

Percentage of work completed for the following commands:

• ALTER INDEX REORGANIZE
• AUTO_SHRINK option with ALTER DATABASE
• BACKUP DATABASE
• CREATE INDEX
• DBCC CHECKDB
• DBCC CHECKFILEGROUP
• DBCC CHECKTABLE
• DBCC INDEXDEFRAG
• DBCC SHRINKDATABASE
• DBCC SHRINKFILE
• KILL (Transact-SQL)
• RESTORE DATABASE
• UPDATE STATISTICS.