7

Is there a good way to do something like the following?

SQLCMD -S %SERVER% -E -Q "BEGIN TRANSACTION"
FOR %%f in (script1 script2 script3) do (
    SET CURRENT_SCRIPT=%%f
    SQLCMD -S Localhost -E -b -V 1 -i %%f.sql -o %%f.log
    IF !ERRORLEVEL! NEQ 0 GOTO ERROR
    SET CURRENT_SCRIPT=
)
SQLCMD -S %SERVER% -E -Q "COMMIT TRANSACTION"
2
  • You might be better off creating a database snapshot beforehand to enable easier rollback; although I have seen snapshots slow down IO intensive workloads, or combining the scripts into one and adding transaction handling. – wBob Feb 23 '15 at 20:34
  • Snapshot is definitely an option. Thanks! – Metaphor Feb 24 '15 at 13:12
10

Because each sqlcmd execution is a separate session, it means you can't have transactions spanning those executions. You're going to want to combine your scripts into a single script for you to do what you want.

If you can properly order your scripts, the following approach in Powershell will work for you:

$scripts = Get-ChildItem "C:\temp\*.sql" |Sort-Object
$fullbatch = @()
$fullbatch += "BEGIN TRANSACTION;"

foreach($script in $scripts){
    $fullbatch += Get-Content $script
}

$fullbatch += "COMMIT TRANSACTION;"

sqlcmd -S localhost -d test -Q "$fullbatch"

Essentially, I read in each script (assuming they are ordered correctly) into a single batch variable, append a BEGIN TRANSACTION and END TRANSACTION to the batch, then execute that back as a query within my sqlcmd execution.

1
  • 1
    Interesting idea. I'd have to modify all the scripts (about 15) to make sure variable names are different to put them in the same batch. Thanks for the suggestion! – Metaphor Feb 24 '15 at 13:11

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.