SQL Server – Using Invoke-SqlCmd with Variables to Output SQL Command Only

automationpowershellsql servert-sqltools

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
EXEC(@kill);
GO
USE [master]

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

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

Best Answer

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"