开发者

Finding the restore progress of SQL server from inside c#

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 fig开发者_运维技巧ure out how to get the progress.

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


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.


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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜