6

The following command in a cmd window

sqlcmd -S. -Usa -Ppass -dmaster -Q "RESTORE DATABASE [MYDATABASE] FROM DISK = 'D:\SQL Server\MYDATABASE.BAK' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10"

displays the following progress output:

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 32320 pages for database 'MYDATABASE', file 'MYDATABASE' on file 1.
Processed 7 pages for database 'MYDATABASE', file 'MYDATABASE_log' on file 1.

But it turns that the progress is shown only after the entire restore, turning the stats during the process useless.

Any advice?

Here is the version of sqlcmd tool:

Microsoft (R) SQL Server Command Line Tool
Version 12.0.2000.8 NT
Copyright (c) 2014 Microsoft. All rights reserved.

Update Dec-2016:

Just including the comment from Microsoft Connect link shared in comments:

SQLCMD was rewritten in SQL 2012 to use ODBC. Here is a small regression error that appears to have sneaked in.

It's the same effect reported when calling RAISERROR('Hello', 0, 1) WITH NOWAIT along a script.

natenho
  • 5,231
  • 4
  • 27
  • 52
  • Having this same problem. I'll post if I find something. – sthames42 Oct 19 '15 at 00:28
  • 1
    Probably because of this https://connect.microsoft.com/SQLServer/feedback/details/765019/raiserror-with-nowait-not-honoured-in-sqlcmd11 – Martin Smith May 11 '16 at 20:06
  • Probably because of your isolation level. – Deadsheep39 Nov 29 '16 at 16:16
  • @Deadsheep39 I'm almost sure that the same command worked fine on previous versions. Could you please elaborate your comment? What does isolation level have to do with such behavior? – natenho Dec 05 '16 at 00:02
  • Whole restore is under implicit transaction. If you would like print result in the middle of transaction you have to use lower isolation level. – Deadsheep39 Dec 05 '16 at 14:00
  • @Deadsheep39 I don't think restore database command messages are related to transaction isolation levels. Could you give some working example or any documentation around the topic? – natenho Dec 07 '16 at 14:30
  • You can also check the SQL Logs - the current log should show the messages in order as they're produced, so at least you can keep track of it manually. – Russell Fox Dec 22 '17 at 01:16

2 Answers2

1

you can query percent_complete in sys.dm_exec_requests

use start to open a separate window and issue a select percent_complete from sys.dm_exec_requests where percent_complete > 0

Computers are lie
  • 11
  • 2
0

I believe you can look in the SQL logs to see the progress ongoing.

Russell Fox
  • 5,273
  • 1
  • 24
  • 28