Reputation: 91
So I am trying to write a Powershell script that creates a backup of a databases, compresses the backup, and uploads it to an FTP site. Here is a part of my script
Sample Script/Code:
Write-Host "Backup of Database " $databaseName " is starting"
push-location
Invoke-Sqlcmd -Query "--SQL Script that backs up database--" -ServerInstance "$serverName"
pop-location
Write-Host "Backup of Database " + $databaseName " is complete"
#Create a Zipfile of the database
Write-Host "Compressing Database and creating Zip file...."
sz a -t7z "$zipfile" "$file"
Write-Host "Completed Compressing Database and creating Zip file!"
I am wanting to prevent any code after the "Invoke-Sqlcmd......." part from being executed until the SQL script backing up the database is complete because the compression line is failing to find the backup of the database because the backup takes a fairly long time to complete.
I am extremely new to using Powershell and didn't quite understand what a couple of the other possibly related questions I found were offering as a solution as I call my SQL statement a different way.
Possible Related Questions:
Get Powershell to wait for an SQL command to execute
Powershell run SQL job, delay, then run the next one
Upvotes: 0
Views: 6566
Reputation: 9292
Are you sure your ...script that backs up the database
isnt just throwing an error and the ps continuing?
This seems to indicate that it does in fact wait on that call:
Write-Host "starting"
push-location
Invoke-Sqlcmd -Query "waitfor delay '00:00:15';" -ServerInstance "$serverName"
pop-location
Write-Host "complete"
In any case, you should guard against the file existing, by either aborting if the file does not exist or polling until it does (i'm not 100% on when the .bak file is written to disk).
# abort
if(!(test-path $file)) {
}
# or, poll
while(!(test-path $file)) {
start-sleep -s 10;
}
Upvotes: 2