Powershell: Issues with 'Write-Progress"

Question

I am making a script to backup databases in SSMS from Powershell. I want to add a progress bar since these databases take some time to backup. This is what I have. The progress bar is popping up but just stays at zero percent. The databases to successfully back up as well. I think I am close but missing a few things in the script.

$SQLServer = "ServerA" 
$Database = 'A', 'B', 'C', 'D', 'E', 'F', 'G' 

$Databases = $Database 
$TotalItems = $Databases.Count
$CurrentItem = 0
$PercentComplete = 0

Foreach ($item in $Databases) 
{
    Write-Progress -Activity "Starting Backups" -Status "$PercentComplete% Complete:" -PercentComplete $PercentComplete
    $Name = $item
    Get-SqlDatabase -Name $Name 
    $CurrentItem++
    $PercentComplete = [int](($CurrentItem / $TotalItems) * 100)
    Start-Sleep -Milliseconds 2500
}

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $Database -InputFile "C:\Users\Documents\Scripts\DatabaseBackups.txt"

Out of interest, why not just use some of the existing tools out there? dbatools, for example gives you a progress bar already. — Thom A
– Thom A ♦, Commented Mar 22, 2023 at 16:50
Primary issue is integer division, you need [int]($CurrentItem * 100.0 / $TotalItems) Also Invoke-SqlCmd itself has no way of tracking progress, so if you were exepecting that then that won't work. I suggest you use proper software for this: SqlBackupMaster is free and works well. — Charlieface
– Charlieface, Commented Mar 22, 2023 at 17:07
If you did migrate to dbatools, then you could do something like: Backup-DbaDatabase -SqlInstance $SQLServer -Database $Database -Path 'YourBackupPath' -Type Full — Thom A
– Thom A ♦, Commented Mar 22, 2023 at 17:10
@ThomA I just installed the module. Is that code all I need to backup the databases, and this will automatically pop up with a progress bar? — movement
– movement, Commented Mar 22, 2023 at 17:29

Mike Q · Accepted Answer · 2023-03-22 19:24:25Z

0

I think the issue is that you are calling the Database task outside of your loop. It doesn't seem like you have the right commands here to complete your task. I'm expecting to see a connection to the DB and a backup operation for each DB.

Anyway, This is all you would need to have the progress bar part of your code to work correctly:

Example of how to step through a loop using Write-Progress

[string]$SqlServer = "ServerA" 
[array]$DatabaseList = 'A', 'B', 'C', 'D', 'E', 'F', 'G' 
[int]$c = 0

Foreach ($Database in $DatabaseList) {
    Write-Progress -Activity "Starting Backups" -Status "Working on : ${SqlServer}  (Database ${Database})" -PercentComplete ((($C++) / $DatabaseList.Count) * 100)
    Start-Sleep -Seconds 2
}

To recap, put your backup commands for ServerA and the Database instead of the for loop.

Unrelated commentary

Lastly, your code uses needless variables. Your forloop shouldn't use 'item' as a var name, use $Database since that is what it is. You had other issues like SQLServer instead of SqlServer and such. Also declare your variables with the types when possible [string], [array] etc..

answered Mar 22, 2023 at 19:24

Mike Q

7,4855 gold badges62 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Q Over a year ago

Thanks vote up the answer if you can

Collectives™ on Stack Overflow

Powershell: Issues with 'Write-Progress"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related