dbatools.io = command-line SQL Server Management Studio: Drop it

This post is part of the series showing practical usage examples. The main post covering links to all posts can be found here: dbatools.io = command-line SQL Server Management Studio: Table of contents.

dbatools commands used in this post:

• Remove-DbaDatabase
• Remove-DbaLogin
• Remove-DbaDbUser
• Remove-DbaDbRoleMember
• Remove-DbaDbRole
• Remove-DbaServerRole
• Remove-DbaAgentJobStep
• Remove-DbaAgentJob

One note before we start dropping objects on the SQL Server. The dbatools commands support a -WhatIf switch (more about that here). I would encourage you to test it out, especially with commands that are removing things.

Drop database

Who does not like to drop databases? Or, better, who have not dropped the database accidentally? That part is for all of you DbDroppers.

In this very first example, I will demonstrate how the -WhatIf switch works. For the other commands, I am going to put the WhatIf message only.

SSMS

dbatools: Remove-DbaDatabase

# use the command with -WhatIf switch
Remove-DbaDatabase -SqlInstance $server -Database TheDatabaseToDrop -Confirm:$false -WhatIf

<# See what is going to be done
What if: Performing the operation "KillDatabase" on target "[TheDatabaseToDrop] on [localhost,1433]".
#>

# now, drop the database without a confirmation prompt -Confirm:$false
Remove-DbaDatabase -SqlInstance $server -Database TheDatabaseToDrop -Confirm:$false

<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance  : 4dc570825344
Database     : TheDatabaseToDrop
Status       : Dropped
#>

Drop login/user/role

Keep the ball rolling and drop some logins, users, even roles. The SSMS screens will look very similar, so I am going to skip some of them.

SSMS

dbatools: Remove-DbaLogin

# drop the login
Remove-DbaLogin -SqlInstance $server -Login LoginToDrop -Confirm:$false 

# What if: Performing the operation "KillLogin" on target "[LoginToDrop] on [localhost,1433]".
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance  : 4dc570825344
Login        : LoginToDrop
Status       : Dropped
#>

SSMS

dbatools: New-DbaDbUser

# drop the user
Remove-DbaDbUser -SqlInstance $server -Database TheDatabaseToDrop -User UserToDrop

#What if: Performing the operation "Removing user from Database [TheDatabaseToDrop]" on target "[UserToDrop]".

<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance  : 4dc570825344
Database     : TheDatabaseToDrop
User         : [UserToDrop]
Status       : Dropped
#>

SSMS

dbatools: Remove-DbaDbRoleMember

Remove-DbaDbRoleMember -SqlInstance $server -Database TheDatabaseToDrop -User UserToDrop -Role db_owner

# What if: Performing the operation "Removing User UserToDrop from role: [db_owner] in database [TheDatabaseToDrop]" on target "[localhost,1433]".

dbatools: Remove-DbaDbRole

# in a very similar way we can drop the database roles
Remove-DbaDbRole -SqlInstance $server -Database TheDatabaseToDrop -Confirm:$false

# What if: Performing the operation "Remove role [DbRoleToDrop] from database [TheDatabaseToDrop]" on target "[localhost,1433]".

dbatools: Remove-DbaServerRole

# ... or server roles
Remove-DbaServerRole -SqlInstance $server -ServerRole ServerRoleToDrop -Confirm:$false

# What if: Performing the operation "Dropping the server-role named ServerRoleToDrop on " on target "".
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance  : 4dc570825344
ServerRole   : ServerRoleToDrop
Status       : Success
#>

Drop agent job/step

Once we dropped all the databases, and logins, why not to cleanup some SQL Agent jobs.

SSMS

dbatools: Remove-DbaAgentJobStep

# drop the step
Remove-DbaAgentJobStep -SqlInstance $server -Job JobToDrop -StepName StepToDrop

# What if: Performing the operation "Removing the job step StepToDrop for job JobToDrop" on target "localhost,1433".

SSMS

dbatools: Remove-DbaAgentJob

# drop the whole job
Remove-DbaAgentJob -SqlInstance $server -Job JobToDrop

# What if: Performing the operation "Removing the job JobToDrop from [localhost,1433]" on target "localhost,1433".

<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance  : 4dc570825344
Name         : JobToDrop
Status       : Dropped
#>

That was quite a cleaning service – we have dropped few interesting objects.

Thank you,
Mikey