0

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?

1

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 > 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:\>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:\>powershell.exe  -command "Invoke-Sqlcmd -ServerInstance '.' -Query '[scutility].[SqlCmdTestOutput]' -Database SCUTILITY -Verbose 4>&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
  • I both love this approach and am scared by it at the same time :D I'll give it a go though and report back. – youcantryreachingme Nov 8 '18 at 21:42
  • @youcantryreachingme - we have not encountered any abnormal issues with this approach. Your mileage may vary. I'd be interested in the results of your testing. – Scott Hodgin Nov 8 '18 at 21:44
  • No success. Downloaded 2008 R2 Command Line Utilities from MS site here (microsoft.com/en-us/download/details.aspx?id=16978) Install said I required Native Client, which I downloaded from same page. Installed both. Found SQLCMD. Confirmed version via license text file. Moved two files to our dev server. Copied your script. Adjusted path. Called `%SQLCMD% with our parameters. Error: application failed to start because its side-by-side configuration is incorrect. MS sol'n requires regedit. Debating my options at this point. – youcantryreachingme Nov 8 '18 at 22:47
  • Following link may help with the error I encountered. Will have to work on this one a little more. blog.sqlauthority.com/2018/03/21/… – youcantryreachingme Nov 8 '18 at 22:57
  • 1
    The invoke-sqlcmd is part of the sqlps powershell module, and doesn't depend on installing the sqlcmd.exe commandline tool. It is a separate installation. If you have neither the commandline tools nor the sqlps powershell module, powershell can use the built-in .NET SqlClient with SqlConnection, SqlCommmand, etc to run commands. – David Browne - Microsoft Dec 20 '18 at 14:25
0

Here's how to do this in PowerShell without the SQL Server commandline tools or the SQLPS module.

This sample runs a single batch and outputs the messages like SSMS does, formatting errors differently from information messages and print output:

$sql = @"
  backup database adventureworksdw2017 
  to disk='c:\temp\aw.bak' with init, format, stats=1

  select 1/0
"@

$constr = "server=.;database=master;integrated security=true"
$con = new-object System.Data.SqlClient.SqlConnection $constr

$con.Open()
$con.FireInfoMessageEventOnUserErrors = $true
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) 

   foreach ( $error in $event.Errors)
   {

     [System.Data.SqlClient.SqlError] $e = $error

     if ($e.Class -gt 10)
     {
       $Host.UI.WriteErrorLine("Msg $($e.Number), Level $($e.Class), State $($e.State), Line $($e.LineNumber)")
       $Host.UI.WriteErrorLine($e.Message)
     }
     else
     {
       Write-Host $e.Message
     }

   }

 }
$con.add_InfoMessage( $handler )

$cmd = $con.CreateCommand()
$cmd.CommandText = $sql 
$cmd.CommandTimeout = 0

$rowcount = $cmd.ExecuteNonQuery()

$con.Close()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.