Sql-server – SQL Server: track database changes

change-trackingsql server

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.

Best Answer

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').

Related Question