CODEWITHSUNDEEP
c#sql-serverbackupprogressdatabase-restore
Scott Chamberlain
Scott Chamberlain

Reputation: 127603

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 figure out how to get the progress.

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

Upvotes: 3

Views: 2994

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294457

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.

Upvotes: 5

Cade Roux
Cade Roux

Reputation: 89741

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

Upvotes: 3

Related Questions