1

I have two very similar database questions related to changes tracking.

  1. There are a lot of automation tests for an application, all of them use one database backup. Currently before each test execution the backup is restored. It's needed as these tests might change data, so if several tests were executed in a row without restoring database, they might fail. Restoring a database is rather slow operation and I want to avoid it. Is there a way to restore a database to the previous state, before executing the test? I want the following scenario:

    • The database is restored
    • Test 1 is executed (it might change data)
    • The database is restored to previous state
    • Test 2 is executed and so on

    I think about wrapping up all tests operation into a transaction and using rollback when a test finishes. But probably there is a better way to do so? Can someone at least point me to the right direction?

  2. I have rather large database which I use for performance testing. Typically, I add experimental indexes, change stored procedures, functions and so on. What I want is to track my changes: which indexes were added, which stored procedures were changed and so on. How can I do it? I want to be able to restore the original database state to compare performance with and without changes.

Improve this question

3 Answers 3

Reset to default
2

You should separate the tests into tests that can execute inside a transaction and rollback and tests that require a full restore. Execute accordingly :)

If you have any DDL in the test then don't bother with transactions, the only safe option is to restore. Most single threaded DML tests will work if wrapped in a transaction and rolled back, but not all.

For speeding up restore consider snapshots, see Revert a Database to a Database Snapshot but keep in mind that snapshot feature requires Enterprise edition (or whatever its current name for 'Enterprise').

Improve this answer
0

Well yo have a few possibilities:

  • If you want to track DDL changes, you can create a DDL Trigger which will capture them and revert them if needed.
  • In case you just need to capture row changes, you can set up CDC (Change Data Capture) which will log changes on a specified table. You can set it up for all needed tables. It will log the value before an UPDATE/INSERT/DELETE and after it. This way you could role it back again.
  • In case you just have a specified state of your database and run a bunch of tests against this specified test, I would just suggest to roll each change back. See example below.

Example for case 3

  • Start test

  • Start transaction

  • Do your changes and tests. Verify your test.

  • Rollback transaction

  • Start the next test

Improve this answer
0

The above given suggestion looks a good start-up into your mentioned scenario. However, I would like to refer you on this informative article that covers the auditing parts and helps to track all critical and granular changes on SQL server with real time alerts : http://sqlserverauditing.blogspot.in/2014/02/track-all-critical-and-granular-changes.html

Improve this answer
1
  • And what does that link say? Please distill the contents and add it to your answer.
    – Colin 't Hart
    Commented Jun 30, 2015 at 10:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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