Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

migrated from Jan 25 '12 at 14:05

This question came from our site for professional and enthusiast programmers.

what about rename table and create new ones from snapshot? Is less expensive rename than delete. You can delete renamed tables one time for day. – danihp Jan 24 '12 at 22:17
what is the fastest way to create new ones from snapshot? – Andrey Shchekin Jan 24 '12 at 22:24
Can you truncate instead of delete? – Blorgbeard Jan 24 '12 at 22:25
@Blorgbeard, We must to ask if tables have foreign keys. – danihp Jan 24 '12 at 22:28
@AndreyShchekin, fast way is insert into ... Select from snapshot. It isn't? This do a bulk insert. – danihp Jan 24 '12 at 22:30

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

share|improve this answer
Great idea if it will work for his needs! – mwigdahl Jan 24 '12 at 22:24
I did it in previous project, unfortunately this has side effects: issues with transaction management in code (for example, using TransactionScope with Suppress overrides it), does not work with FTS, has issues with multi-threaded tests. – Andrey Shchekin Jan 24 '12 at 22:43
@AndreyShchekin What about using transactions where possible, but then using your existing cleanup method for tests that aren't compatible with transactions? That could cut down on the run time for the suite of tests... – Michael Fredrickson Jan 24 '12 at 22:50
@MichaelFredrickson unfortunately it creates a lot of work for developers not all of whom are aware on why that is even needed. for example, component A calls component B calls component C that suppresses the transaction for a certain reason. Or does an insert, then forks in two threads to do other operations expecting INSERT to be complete and non-locking. Then all tests on component A would have to know about that. – Andrey Shchekin Jan 24 '12 at 22:53

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?

share|improve this answer
How is this different from executing this SP myself? My current DELETE and INSERT statements are already in a SP (just for convenience). – Andrey Shchekin Jan 24 '12 at 22:17
Running this SP takes 1 sec in Management Studio as well. – Andrey Shchekin Jan 24 '12 at 22:19
the difference is that after execing the sp, it can copy data from your source snapshot to your destination tables. It might not be any faster, just automates the process. – Beth Jan 24 '12 at 22:33

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?
share|improve this answer
Yes I can, though it is a lot of complexity just for the sake of the tests, I would prefer more straightforward solution if possible. This will require me to put triggers or change tracking on all tables and check it correctly. – Andrey Shchekin Jan 24 '12 at 22:50

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.


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.


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!

share|improve this answer
How do I do a nonlogged DELETE+INSERT in SQL Server? – Andrey Shchekin Jan 24 '12 at 22:54
TRUNCATE is not "nonlogged" -… I'll agree that it is LESS logged, but it is incorrect to say it is not logged. – Aaron Bertrand Jan 25 '12 at 1:05
As @Aaron said, there is no such thing as a non-logged user operation in SQL Server. TRUNCATE and SELECT INTO are minimally logged, meaning they can be rolled back but generate fewer log records for the work they do. – Nick Chammas Jan 25 '12 at 16:18

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.