Batch database creation using .BAK backups

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?

Solution

I would suggest you utilize Database Snapshots instead. This allows you to take a snapshot of the database, and then revert back to it after changes are made. The disk space taken up by the snapshot is purely the difference in changes to pages, not the whole database.

Here is a script to create database snapshots for all user databases (you cannot do this for system DBs).

DECLARE @sql nvarchar(max);

SELECT @sql =
  STRING_AGG(CAST(CONCAT(

    'CREATE DATABASE ',
    QUOTENAME(d.name + '_snap'),
    ' ON ',
    f.files,
    ' AS SNAPSHOT OF ',
    QUOTENAME(d.name),
    ';'
   )
   AS nvarchar(max)), '
'  )
FROM sys.databases d
CROSS APPLY (
    SELECT
      files = STRING_AGG(CONCAT(

        '(NAME = ',
        QUOTENAME(f.name),
        ', FILENAME = ''',
        REPLACE(f.physical_name + 'snap', '''', ''''''),
        ''')'

       ), ',
'    )
    FROM sys.master_files f
    WHERE f.database_id = d.database_id
      AND f.type_desc = 'ROWS'
) f
WHERE d.database_id > 4;  -- not system DB

PRINT @sql;

EXEC sp_executesql @sql;

And here is a script to revert to the snapshots

DECLARE @sql nvarchar(max);

SELECT @sql =
  STRING_AGG(CAST(CONCAT(

    'RESTORE DATABASE ',
    QUOTENAME(dSource.name),
    ' FROM DATABASE_SNAPSHOT = ',
    QUOTENAME(dSnap.name),
    ';'
   )
   AS nvarchar(max)), '
'  )
FROM sys.databases dSnap
JOIN sys.databases dSource ON dSource.database_id = dSnap.source_database_id;

PRINT @sql;

EXEC sp_executesql @sql;

And to drop the snapshots:

DECLARE @sql nvarchar(max);

SELECT @sql =
  STRING_AGG(CAST(CONCAT(

    'DROP DATABASE ',
    QUOTENAME(d.name),
    ';'
   )
   AS nvarchar(max)), '
'  )
FROM sys.databases d
WHERE d.source_database_id > 0;

PRINT @sql;

EXEC sp_executesql @sql;