SQL Powershell Delete Database

Question

Looking to see if this is possible. Our SQL server(s) have numerous databases from our customers where we are doing tech support for them. Once the support issues is done (3 months after resolved), we want to automatically delete the database (not just drop, but delete the actual files as well to reclaim disk space).

Our databases are all named in a particular format that matches the customers incident number. So we should be able to query the list of support issues resolved and compare that to the databases names attached to the SQL instance(s).

I am new to powershell, just wondering if such a thing would be possible as a script that could be run on a weekly basis.

Thank you all.

You may want to have a look at the dbatools PowerShell module — iamdave
– iamdave, Commented Jan 7, 2021 at 14:30
Creating a new database for each ticket feels like a design flaw. — Thom A
– Thom A ♦, Commented Jan 7, 2021 at 14:30
One would imagine this is a copy of their customer's database as at the support ticket for investigation, which is even more of a design flaw. — iamdave
– iamdave, Commented Jan 7, 2021 at 14:32
Why don't you use SQLAgent which is dedicated to such a job ? And optimized for ??? — SQLpro
– SQLpro, Commented Jan 7, 2021 at 14:34
We keep separate databases as this is a massive application and knowing what happened at that exact time and what the data looked like is important. Customers tend delete transactions and try again which removes the affected data in some cases. — Chappy
– Chappy, Commented Jan 7, 2021 at 16:04

Robert van den Berg · Accepted Answer · 2021-01-07 15:14:13Z

3

Yes, you can do this with PowerShell. With invoke-sqlcommand you can run any query you like, including dropping databases. So something like this:

Invoke-SQLCommand -ServerInstance MyServer\Instance -database master -query "DROP DATABASE [MyUnsupportedDatabase]"

Under normal circumstances, the database files will automatically be removed.

Needless to say, before you start deleting databases with a script, you will want to test this thoroughly.

answered Jan 7, 2021 at 15:14

Robert van den Berg

1063 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chappy Over a year ago

Thanks for the reply, that will get me going to go back to my team to tell them it can be done (politics...lol).

Collectives™ on Stack Overflow

SQL Powershell Delete Database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related