Powershell script to delete SQL server databases matching a filter

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
Advertisements

2 thoughts on “Powershell script to delete SQL server databases matching a filter

  1. Thank you for the script.

    Found 1 issue in the script:

    msbd.dbo.sp… must be msdb.dbo

    Errors
    Invoke-SQLcmd -ServerInstance $ServerInstance -Query (“EXEC msbd.dbo.sp_delete_database_backuphistory @database_name = N'” + $Database.Name + “‘”)

    Working
    Invoke-SQLcmd -ServerInstance $ServerInstance -Query (“EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'” + $Database.Name + “‘”)

    after changing the msbd to msdb the script worked like a charm.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s