I have a big *.sql file that I run with Invoke-Sqlcmd and variables to do some tasks, and when I have an issue, it's a pain to debug because of the variables and I want to do logging of the SQL script about to be run.

Is there a reasonably native way to produce the final SQL about to be executed with variable substitution without actually running?

Here's a sample I run to restore a *.bak to various environments for example:

Invoke-Sqlcmd -InputFile $sqlFile -QueryTimeout 0 -ServerInstance 'SQLServer' -Database 'Master' -Variable @("DatabaseBAK=$TransactDB", "Database=$TargetDatabase") -Verbose

SQL File:

USE [master]

print 'Killing connections to database $(Database)'
DECLARE @kill varchar(8000) = '';

SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('$(Database)') 
 and session_id <> @@SPID
USE [master]

print '[Info] RESTORE DATABASE [$(Database)]'
print '[Info] FROM DISK = N''$(DatabaseBAK)'''
print 'Starting restore-------------------->'
FROM DISK = N'$(DatabaseBAK)'
    ,STATS = 5
print '<--------------------End restore'

I would like to somehow just output the above with variable substitution the way Invoke-SqlCmd does it.


I don't think invoke-sqlcmd can do thi. Regular sqlcmd.exe has the -e parameter to output the batches, although it will not output the "GO" . EG

sqlcmd -e -i restore.sql -v database="foo" databaseBak="bk" > Restore.final.sql

You can put SET NOEXEC ON at the top, or just point it at an dummy instance to generate the script.

Or something like:

sqlcmd -e -Q "set noexec on;`n:r restore.sql" -v database="foo" databaseBak="bk"
| improve this answer | |

Instead of reinventing the wheel, just use dbatools - restore-dbadatabase

it has extensive coverage of all the scenarios that you might ever need.

Note: dbatools also has a better version of invoke-sqlcmd --> invoke-sqlcmd2 with a -ParseGO to parse scripts with GO batch seperators and is more optimized version.

| improve this answer | |
  • I'll check it out. The script is just an example though. I have some more complex ones that are doing a bunch of different things in automated code build processes for different environments. – Alex K Jun 1 '18 at 22:25
  • dbatools module will not output those PRINT statements...just an FYI. You have to handle that type of output differently in PS when you use something outside of Invole-Sqlcmd – user507 Jun 6 '18 at 13:57

I figured out a clever way with set quoted_identifier off

enter image description here


$sqlFile = "C:\Users\user\Desktop\TestOutput.sql"
$begin = 'set quoted_identifier off print "'
$end = '" set quoted_identifier on'

Invoke-Sqlcmd -InputFile $sqlFile -QueryTimeout 0 -ServerInstance 'Server' -Database 'Database' -Variable @("begin=$begin", "end=$end") -Verbose

$begin = '--Begin'
$end = '--End'

Invoke-Sqlcmd -InputFile $sqlFile -QueryTimeout 0 -ServerInstance 'Server' -Database 'Database' -Variable @("begin=$begin", "end=$end") -Verbose


$(begin) is either
    set quoted_identifier off print "

select SALESID from SALESTABLE where SALESID = 'SO-000003'
select SALESID from SALESTABLE where SALESID = 'SO-000004'

$(end) is either
    " set quoted_identifier on
| improve this answer | |

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.