Batch database creation using .BAK backups

Question

I'm preparing to test an application in development. The application uses SQL Server 2019 for backend databases. It allows users to maintain multiple databases (for compliance and regulatory reasons).

QA testing scenarios require databases to be restored frequently to a known state before a staff member performs test cases in sequence. They then note the results of the test scenario.

There are approximately a dozen test scenarios to work on for this release, and an average of 6 databases to be used for most scenarios. For every scenario, this setup takes about 10 minutes and involves over 20 clicks.

Since scenarios will be tested before and after code changes, this means a time commitment of about 8 hours on setup alone. I suspect this can be reduced to about 1 minute since most of the time is spent navigating menus and the file system while restorations only take a few seconds each.

So I'd like to automate restorations. How can I automate the following sequence of operations inside of SSMS?

Drop all user created databases on the test instance of SQL Server
Create new or overwritten databases populated from ~6 .BAK files. I currently perform this one-by-one using "Restore Database", then adding a new file device, and finally launching the restorations.

EDIT: I usually work with SQL, C#, Batchfiles, or Python. But this task allows flexibility as long as it saves time and the restoration process is reliable. I would imagine either SSMS or a T-SQL query are the natural first places for me to begin.

We are currently using full backups and these seem to remain connected to their parent SQL Server instance and database. This caused me to encounter an SSMS bug when attempting to overwrite an existing database with a backup from another database on the same instance -- the restore fails to overwrite the target database, and the database that created the backup becomes stuck "restoring" until SSMS is closed or I manually restore it with the correct backup.

So as a minor addendum, what backup settings are appropriate for creating these independent copies of databases that have been backed up from other SQL Server instances?

Automate it how? Do you have a favored programming language and could you deploy a app? Do you have some kind of pipeline that can run scripts, for example PowerShell? Do you require that it be done through a T-SQL script that's run in SSMS? If it were up to me I'd seek to eliminate all of those staff members that are performing tests as well, and (at most) have them curate an automated test runner instead (as in, ensuring no changes to it are made and what you see in the tests is what you get). Leave no room for fat fingers to fumble. — Jeroen Mostert
To a first approximation, automating your current restores is as simple as using that handy "Script" button that SSMS produces at the top of the window, when you're done configuring all the bells and whistles, right before you hit "OK", and keeping the location of your backups constant. Making this more fancy to account for variables would be step 2. This would also allow you to inspect and manually run the statements to see what errors occur - a DB getting stuck in the "restoring" state is normally not an SSMS bug but a user error of some sort. Backups are not associated with servers. — Jeroen Mostert
Ah I see. I'm most familiar with the SSMS user interface. But I'm more satisfied with any method of automation as long as I can dump the .BAKs in one directory and launch the restore with minimal clicks to overwrite/create any databases. So if it can be done using a SQL query (or PowerShell) and less than a few dozen lines of boilerplate, that's more than great! — Aaron