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
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.