Can I use Invoke-Sqlcmd to invoke the SQL and then quit?

Question

Can I use Invoke-Sqlcmd to invoke the SQL and then quit?

praveen_vejandla

SSCrazy

Points: 2069
More actions
October 25, 2011 at 4:37 am

#250712

Is there any option that I can pass to Invoke-Sqlcmd such that it invokes the SQL code but do NOT wait till SQL gets executed completely?
I am trying to write a powershell script for backup but I don't want the script to wait till completion of backup.
I want to just issue the backup command to SQL Server and then script should either quit or proceed to next line in the powershell script. If I am backing up a large database, I don't want my script to wait for long hours, just display a message that backup has been started and quit.
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

praveen_vejandla (10/25/2011)
Is there any option that I can pass to Invoke-Sqlcmd such that it invokes the SQL code but do NOT wait till SQL gets executed completely?
I am trying to write a powershell script for backup but I don't want the script to wait till completion of backup.
I want to just issue the backup command to SQL Server and then script should either quit or proceed to next line in the powershell script. If I am backing up a large database, I don't want my script to wait for long hours, just display a message that backup has been started and quit.
Thanks.

Hmmm, not from what I can see...nothing I can see will let you kick off a script and exit the PowerShell session and have the SQL request keep going. Whether run asynchronously or synchronously the call to Invoke-Sqlcmd appears to require a parent PowerShell session until it completes. You can kick off the backup command asynchronously within PowerShell and then do other work in the session while its running, but from what I can tell the overall PowerShell session has to stay alive until that async call completes. Maybe someone with more PS knowledge can educate us both.

Here are a couple alternatives that may work for you, maybe there are more:

1. Leverage SQL Agent. msdb.dbo.sp_start_job is asynchronous. When called it starts the requested Agent job and immediately returns control to the caller with the return code noting whether the job could be started, i.e. it does not wait for the job to complete. This may not work for you if you are building your backup command on the fly in PowerShell but your process might be re-workable to use this approach.

2. Use Service Broker to gain asynchronous behavior within SQL Server.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

praveen_vejandla SSCrazy Points: 2069 More actions · Answer 2

Thanks for the reply. How can I kick off the backup command asynchronously within PowerShell and then do some other work without waiting for backup to complete.

Thanks.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Have a look at the Start-Job CmdLet. Also, check the results of your favorite search engine when entering "asynchronous powershell".

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato