Deans Blog

I previously shared a SQL server script to bulk delete all SQL server databases which matched a search filter (http://tinyurl.com/oucvxfs), I have recently written a powershell script to perform the same process and to remove the database backup history.

Firstly, we will need to specify the parameters required to run the script.

Param ([Parameter(Mandatory=$true)][string] $ServerInstance,
[Parameter(Mandatory=$true)][string] $Filter)

In order to invoke SQL statements from within Powershell, we will need to import the SQLPS module into the current powershell session:

Import-Module SQLPS -DisableNameChecking

In order to return all databases which match the filter specified as a parameter, we will invoke a SELECT statement to return the information from the sys.databases view:

$Databases = Invoke-SQLcmd -ServerInstance $ServerInstance -Query ("SELECT * from sys.databases where NAME like '%$Filter%'")

For each line returned in the database variable, we will loop through each one using the name value to determine the database to remove the backup history and then finally drop the database.

ForEach ($Database in $Databases)
{ 
Invoke-SQLcmd -ServerInstance $ServerInstance -Query ("EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'" + $Database.Name + "'")
Invoke-SQLcmd -ServerInstance $ServerInstance -Query ("DROP DATABASE [" + $Database.Name + "]")

You can run the script as below, to connect to the server instance SERVER1\SQL and remove database containing the string ABCD:

./Remove-BulkSQLServerDatabases.ps1 -ServerInstance SERVER1\SQL -Filter ABCD