1

When I use T-SQL in SSMS to restore a database with the STATS = 10 option (as per example below), I get statistics every 10 percent printed in the Messages window. I'm now wanting to automate the restore process (point a VB.Net WinForm application at a directory of backup and transactions files and let it restore all the data) and want to report the progress in a standard Windows form progress bar.

RESTORE DATABASE [Databasename] FILE = N'DatabaseName_dat' FROM  DISK = N'S:\SQL\Backups\DatabaseName.bak' WITH FILE = 1, MOVE N'AuctionData_dat' TO N'C:\SQL\Data\DatabaseName.mdf', MOVE N'DatabaseName_log' TO N'C:\SQL\Logs\DatabaseName.ldf', NOUNLOAD, REPLACE, STATS = 10

I'm guessing I need to use some form of async command and wait for each response from the server to update the progress bar. So far all I've seen though are ExecuteReaderAsync, ExecuteNonQueryAsync and ExecuteScalarAsync commands - none of which return any details until the command has completely finished.

Is this even possible from .Net?

Thanks for any help or tips

bmadtiger
  • 671
  • 6
  • 17
  • Have you looked at SMO for doing the restore? It has a PercentComplete event that you could probably subscribe to and act accordingly. https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.restore.aspx – Ben Thul Jan 27 '15 at 18:32
  • Thanks Ben - a .Net solution is what I was hoping to find. I have seen someone else mention SMO but the documentation didn't appear all that clear. Your link is much better. – bmadtiger Jan 27 '15 at 22:04
  • SMO is the .NET API for SQL Server. So you get the best of both worlds. – Ben Thul Jan 27 '15 at 23:22

2 Answers2

3

This isn't a .Net question per se. Your question is actually "Can I write a SQL script to perform a restore, and then periodically query its progress while the restore is running?"

The answer is "Yes". Look here for details:

Is there a SQL script that I can use to determine the progress of a SQL Server backup or restore process?

You must do the following:

1) Launch your script (you can do this manually, or do it from your WinForms app)

2) Pass the session ID to your Winforms

3) Put a timer in your winforms app to periodically call sp_who.. or SELECT command, percent_complete, start_time FROM sys.dm_exec_requests WHERE session_id = ... and display the "%/done" graphically.

Community
  • 1
  • 1
FoggyDay
  • 11,962
  • 4
  • 34
  • 48
  • Thanks @FoggyDay - that's good thinking outside of the box. I was hoping for a .Net solution to the problem, and it appears from Ben's comment that SMO is the way to go for that. Still good to know about these built in queries that will work for any framework. – bmadtiger Jan 27 '15 at 22:07
2

This query will give you the percentage complete for the session. You might be able to execute this query and get the percent_complete value and use that to update your progress bar

SELECT percent_complete
FROM sys.dm_exec_requests
WHERE session_id=<your restore session id>
Raj
  • 10,653
  • 2
  • 45
  • 52