Search code examples
sqlsql-serversqlcmd

What is the fastest way to return a SQL Server database to a point in time?

The business problem: we would like to run a series of automated tests, each against a database, starting in a known state.

Right now, the best way I know to guarantee that the database is in its original state at the start of a test is to restore the database from a .bak backup file prior to each test, but this is slow.

Is there a faster way?

We are using SQL Server 2016, and primarily .NET technology.

(Note: I know there are more database-centric forums, but since I am asking from a developer point of view it seems reasonable to start here.)

Solution

  • Database snapshotting appears to do exactly what I want with minimal overhead.

    Create snapshot:

    CREATE DATABASE t19314_ss ON
  ( NAME = TimeStar,
    FILENAME = 'C:\temp\snappy.ss' ) AS SNAPSHOT OF t19314

    Revert to snapshot:

    RESTORE DATABASE t19314 from DATABASE_SNAPSHOT = 't19314_ss';