question

Nibbler avatar image
0 Votes"
Nibbler asked Nibbler commented

Delete multiple Azure SQL's with PowerShell

Hi all

I have this (below) basic script to delete a single SQL database. But, would really like to be able to delete all SQL's where "Backup" is included in the name...so, a single script to delete them all.

Example:
Backup-SQL-01-01012021
Backup-SQL-02-01012021
Backup-SQL-03-01012021
Backup-SQL-04-01012021
Backup-SQL-05-01012021

The number of SQL's could be +100.

Script to delete SQL
Remove-AzSqlDatabase -ResourceGroupName "ResourceGroup-Backup" -ServerName "BackupServer" -DatabaseName "Backup-SQL-01-01012021" -AsJob -Force

Ang great ideas to do this, and if it's possible?

windows-server-powershellazure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndreasBaumgarten avatar image
0 Votes"
AndreasBaumgarten answered Nibbler commented

Hi @KE1980 ,

I just did some testing in my environment. Both scripts are working here now.
In the first script I have to remove the quotes around the $_.DatabaseName

 Get-AzSqlDatabase -ResourceGroupName "ResourceGroup-Backup" -ServerName "backupserver" -DatabaseName "Backup*" |
     ForEach {
      $_.DatabaseName # | Select *
     Remove-AzSqlDatabase -ResourceGroupName "ResourceGroup-Backup" -ServerName "backupserver" -DatabaseName $_.DatabaseName -Force
 }


And the second script is working from the beginning:

 Get-AzSqlDatabase -ResourceGroupName "ResourceGroup-Backup" -ServerName "backupserver" -DatabaseName "Backup*" | 
         Remove-AzSqlDatabase -Force



(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AndreasBaumgarten

It`s work perfectly. Thank you for your help!

0 Votes 0 ·
AndreasBaumgarten avatar image
0 Votes"
AndreasBaumgarten answered Nibbler commented

Hi @KE1980 ,

you can give it a try with this. Run the script on your own risk. The script is not tested by myself!

 Get-AzSqlDatabase -ResourceGroupName "ResourceGroup-Backup" -ServerName "BackupServer" -DatabaseName "Backup-SQL*" | 
 ForEach-Object {
     Remove-AzSqlDatabase -ResourceGroupName "ResourceGroup-Backup" -ServerName "BackupServer" -DatabaseName "$_.DatabaseName" -AsJob -Force -WhatIf
     }

The script above will not delete the databases as long as -Whatif is at the end of line 3. This way you can check what will happen ;-)
If the script is working as required you can just remove the -Whatif in line 3


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AndreasBaumgarten

Thanks for helping. I had a go at you exsample.

The "Get-AzSqlDatabase" works fine, in such one can see that "Backup-SQL*" is getting alle the DB`s where "Backup-SQL" is part of the name.

The "Remove-AzSqlDatabase" gives an error:

The Resource 'Microsoft.Sql/servers/BackupServer/databases/Backup-SQL*' under resource group ResourceGroup-Backup' was not found. For more details please go to https://aka.ms/ARMResourceNotFoundFix


.

0 Votes 0 ·
AndreasBaumgarten avatar image
0 Votes"
AndreasBaumgarten answered AndreasBaumgarten commented

Hi @KE1980 ,

Could you please check the value of $_.DatabaseName in the `ForEach-Object" loop.
It should be the name of the Database to delete.

And just for testing you can try this:

 Get-AzSqlDatabase -ResourceGroupName "ResourceGroup-Backup" -ServerName "BackupServer" -DatabaseName "Backup*" | 
     Remove-AzSqlDatabase -WhatIf


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AndreasBaumgarten

The test script works:

Get-AzSqlDatabase -ResourceGroupName "ResourceGroup-Backup" -ServerName "BackupServer" -DatabaseName "Backup*" |
Remove-AzSqlDatabase -WhatIf

0 Votes 0 ·

@KE1980 ,

so your question is answered and the second script is running without a problem?
If you remove the -Whatif in line 2 it should delete all database with Backup in the name.


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

0 Votes 0 ·