What is the fastest way to reset SQL Server database if you can not use RESTORE?

Question

I have integration tests that require test database to function.
Since tests should generally be independent, I reset the database in the beginning of each test.

I can not use RESTORE, since some parts of architecture (which I do not control) are caching the connections, and will fail with connection lost on next call.

Right now I am creating a snapshot, then calling DELETE + INSERT on each table to synchronize data with snapshot. However, it takes 1 second per reset which is way too much (150 tests = 150 seconds). I have a lot of tables, but they are almost empty so there is no reason for it to be so slow.

So how can I replace database with its previous version in less than 1 second without losing connections?

My next idea would be to add some kind of change tracking since each test only affects some tables, but it would make reset code even more complicated.

UPDATE: I added SET STATISTICS TIME ON and I am getting

SQL Server parse and compile time: 
   CPU time = 327 ms, elapsed time = 343 ms.

for my reset SP. I assume it is due to ALTER TABLE ... NOCHECK CONSTRAINT ALL calls in the beginning of the SP. I wonder if it is possible to suppress recompilation in this case.

Answer 1

Run each of your tests inside of a single transaction... at the end of each test, ROLLBACK the transaction.

Answer 2

Use an SSIS package to exec an sp which deletes all the rows from each of the tables, then restores them from your snapshot source?

Answer 3

I know it's an old topic but times have changed and here is what is possible now.

1. You could create and use a dockerimage with the Database State you need for the tests and run a new container for eacht test. Pro: You can run multiple tests in parallel because you have more than one database Pro: You can be sure you'll get the desired state! Con: If you don't run it in parallel the Container Startup could take a while

2. You can create a Dump and reset the database with this dump before each test Pro: You can be sure you'll get the desired state! Pro/Con: (Depending of the amount of data) the reset may take a while

3. Combine both ways and run the Tests in Parallel. You could keep the prepatched Docker container per thread/process/cpu/whatever and reset it with the dump between each test. This may be faster depending on the amount of data as well

Depending on your setup you could manage the Containers with tools like "testcontainers" if you want to make sure the containers are stopped afterwards

Answer 4

How much data across how many table are we talking about here? Depending on volume, doing it all in under 1 second might be a pretty tall order.

If you have 150 tests, and each test must be run on the exact same set of data, that means you have to undo the effects of (say) test N before you can run text N_1. However, this also implies that, as you are doing testing, you know the before/after state of the data. Can you incorporate something like:

• Perform Test
• Check Results
• Reset data back to initial state by undoing only what just changed?

Answer 5

If your database is just for testing, you can go to a more minimally-logged operation, which might speed up the DELETE and INSERT behavior substantially.

EDIT:

You wouldn't actually DELETE and INSERT if you wanted the less logging on your operations. You can TRUNCATE TABLE instead of DELETE, and SELECT INTO instead of INSERT. Unfortunately, you can't SELECT INTO a table that already exists, so you'd either have to rename or drop the existing table and then SELECT INTO.

EDIT 2:

Modified answer to reflect the fact that TRUNCATE AND SELECT INTO are not truly non-logged, although they incur less log overhead than DELETE and INSERT do.

Thanks to the folks that corrected me!