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
精彩评论