How to Drop SQL database using PowerShell

Thiyagu

3 months ago

My Scenario:

As a System Admin/DevOps Guys, we got urgent cost optimization process and it need to done in very less time frame. From the cost optimization list, one of the action is to clean the unused database and backup file across all the environment. So we having 100+ database in each environment and its difficult to make manual clean up as it will take more time and there is lot of chance to wrong deletion of used database’s. So to avoid this we thought to automate this process to clean up database’s across all the environment. In this post we will discuss about how to How to drop SQL database using PowerShell

If you are working with Azure SQL Databases and want to use Azure PowerShell (Az module), you can use the Get-AzSqlDatabase cmdlet to retrieve information about SQL databases in an Azure SQL Server. Here’s an example script to get the list of all SQL database names:

Step 1: Declare SQL and resource details


#Assign the variables
$resourcegroup = "rg-dgtl-strg-prd-we-01"
$dbserverName = "sqlsrvr-dgtl-prd-we"
$username = "sqlprd01"
$password = "Srdc4$wm2t1F"

#Assign the variables

$resourcegroup = "rg-dgtl-strg-prd-we-01"

$dbserverName = "sqlsrvr-dgtl-prd-we"

$username = "sqlprd01"

$password = "Srdc4$wm2t1F"

Step 2: Connect to the database using Get-AzSqlDatabase cmdlet

The Get-AzSqlDatabase cmdlet is used in Azure PowerShell to retrieve information about SQL databases (as shown in the below snap shot) in an Azure SQL Server. It’s part of the Az module, which is the recommended module for managing Azure resources. Below is a brief explanation of how to use the Get-AzSqlDatabase cmdlet:

SYNTAX:

Get-AzSqlDatabase
[[-DatabaseName] <String>]
[-ExpandKeyList]
[-KeysFilter <String>]
[-ServerName] <String>
[-ResourceGroupName] <String>
[-DefaultProfile <IAzureContextContainer>]
[-WhatIf]
[-Confirm]
[<CommonParameters>]


#Get the all the database for specific SQL server using -ServerName parameter
$SQLdbs = Get-AzSqlDatabase -ServerName $dbserverName -ResourceGroupName $resourcegroup

#Get the all the database for specific SQL server using -ServerName parameter

$SQLdbs = Get-AzSqlDatabase -ServerName $dbserverName -ResourceGroupName $resourcegroup

Step 3: Retrieve all database’s details Using foreach.

In step 1, we got all the database details which present inside sqlsrvr-dgtl-prd-we SQL server and as i said on top, I am having 100+ database present in the sql server so below loop will loop one by one to process the db’s details. Below, i am getting only database name for db using property name like “DatabaseName”


#Loop the list of databases and check on one by one
foreach ($SQLdb in $SQLdbs){
$SQLdb = $SQLdb.DatabaseName.ToString()
if (..) { check databasename contains or -eq to find activie dbs. }
else { logic to delete the non-active dbs. }
}

#Loop the list of databases and check on one by one

foreach ($SQLdb in $SQLdbs){

$SQLdb = $SQLdb.DatabaseName.ToString()

if (..) { check databasename contains or -eq to find activie dbs. }

else { logic to delete the non-active dbs. }

}

Step 4: Remove the database using Remove-AzSqlDatabase

The Remove-AzSqlDatabase cmdlet removes an Azure SQL database. This cmdlet is also supported by the SQL Server Stretch Database service on Azure.

SYNTAX :

Remove-AzSqlDatabase
[-DatabaseName] <String>
[-Force]
[-ServerName] <String>
[-ResourceGroupName] <String>
[-DefaultProfile <IAzureContextContainer>]
[-WhatIf]
[-Confirm]
[<CommonParameters>]


#Remove the database based on -DatabaseName parameter
Remove-AzSqlDatabase -ResourceGroupName $resourcegroup -ServerName $dbserverName -DatabaseName $dbName

#Remove the database based on -DatabaseName parameter

Remove-AzSqlDatabase -ResourceGroupName $resourcegroup -ServerName $dbserverName -DatabaseName $dbName

Points to remember:

I am running above script inside my jump/AVD machine so if required please use -DefaultProfile parameter in the Get-AzSqlDatabase / Remove-AzSqlDatabase to authenticate the SQL server. This -DefaultProfile parameter used with credentials, tenant and subscription used for communication with azure.