How to report stats on restore via sqlcmd? I've inherited a batch file script that calls sqlcmd with parameters. One of the parameters is the path to a SQL file. That file includes a database restore command. The command in the SQL file includes the parameter stats = 1 but nothing is reported in the commandline window during a restore.

Is it possible to use sqlcmd this way to initiate a database restore and have the stats report back and displayed on the commandline as the restore executes? If not, then can you point me to alternative approaches?
UPDATE: 2018-12-20 - I completely re-wrote my answer to use Powershell.  Perhaps this solution will work for you.



I believe SQLCMD was rewritten for 2012 in such a way that print statements and even raiserror messages issued during a run via SQLCMD never displayed until the entire job finished.

If using Powershell is an option, you can use the Invoke-SqlCmd cmdlet to run your scripts.  To see the output while the script is running, include the -Verbose parameter.  (you'll also want to include something like -QueryTimeout 0 to keep the script from timing out at the default 30 seconds)

Here is a quick way to test this functionality.

Create the following stored procedure which issues raiserror statements every 10 seconds (for 50 iterations) and displays the current timestamp.  Under normal runs of SQLCMD, you wouldn't see any output until the procedure completely finishes.  However, using INVOKE-SQLCMD and the -Verbose parameter, you will see the output as the script runs

DROP PROCEDURE IF EXISTS [scutility].[SqlCmdTestOutput]
GO
CREATE PROCEDURE [scutility].[SqlCmdTestOutput]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @count INT = 50
    DECLARE @msg VARCHAR(8000)

    WHILE @count &gt; 0
    BEGIN
        SET @msg = convert(VARCHAR(50), sysdatetime())

        RAISERROR (@msg,0,1) WITH NOWAIT

        WAITFOR DELAY '00:00:10'

        SET @count -= 1
    END
END




--The powershell command
powershell.exe  -command "Invoke-Sqlcmd -ServerInstance '.' -Query '[scutility].[SqlCmdTestOutput]' -Database SCUTILITY -Verbose -QueryTimeout 0"

--output
C:\&gt;powershell.exe  -command "Invoke-Sqlcmd -ServerInstance '.' -Query '[scutility].[SqlCmdTestOutput]' -Database SCUTILITY -Verbose -QueryTimeout 0"
VERBOSE: 2018-12-20 08:12:24.3689374
VERBOSE: 2018-12-20 08:12:34.3696751
VERBOSE: 2018-12-20 08:12:44.3704152
VERBOSE: 2018-12-20 08:12:54.3710561
VERBOSE: 2018-12-20 08:13:04.3717697




You can also eliminate the string 'VERBOSE:' from the output by redirecting the -Verbose output and using Write-Host.

C:\&gt;powershell.exe  -command "Invoke-Sqlcmd -ServerInstance '.' -Query '[scutility].[SqlCmdTestOutput]' -Database SCUTILITY -Verbose 4&gt;&amp;1 -QueryTimeout 0 | % {Write-Host ($_.Message)}"
2018-12-20 08:42:59.9874559
2018-12-20 08:43:09.9891544
2018-12-20 08:43:19.9898284
2018-12-20 08:43:29.9905699
2018-12-20 08:43:39.9912415
2018-12-20 08:43:49.9919723
2018-12-20 08:43:59.9936545
2018-12-20 08:44:09.9943466
2018-12-20 08:44:19.9960623
2018-12-20 08:44:29.9967689