CODEWITHSUNDEEP
sql-serverpowershell
sam yi
sam yi

Reputation: 4934

powershell sqlbackup and copy file

I'm writing a PS script to back up a database and copy the .bak over to a different server. However, the backup seems to be happening asynchronously. And sometimes it reaches the copy command pre-maturely.

I've tried these suggestions here but it didn't quite work.

sqlbackup.Wait() documentation here... seems to do exactly what I'm looking to do (at least on paper) but it doesn't seem to work. It still reaches my copy command before the file gets generated. Any idea how to get this to work... here is what I'm doing.

$smoBackup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full Backup of " + $dbName
$smoBackup.BackupSetName = $dbName + " Backup"
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = "Disk"
$smoBackup.Devices.AddDevice($sourcePath, "File")
$smoBackup.CopyOnly = "true"
$smoBackup.SqlBackup($SourceSrv)

Copy-Item -Path $sourcePathUNC -Destination $destinationPathUNC

Upvotes: 0

Views: 1076

Answers (1)

TheMadTechnician
TheMadTechnician

Reputation: 36277

Easily enough worked around... Just tell it to wait. Before your Copy-Item drop this in:

While(!(Test-Path $sourcePathUNC)){Start-Sleep -M 500}

So that way, as long as it doesn't see the file it waits half a second, and then tries again.

Upvotes: 1

Related Questions