Alternatives to snapshot functionality, SQL Server Standard

sql-server database testing sql-server-2008-r2 snapshot
Sunib picture Sunib · Mar 14, 2011 · Viewed 8.1k times · Source

For testing purposes I would like to reset a complete database to a certain state (data and structure). I would like to do this automated. Preferably a command line instruction before I start my tests.

After some searching I discovered that SQL Server offers an great solution for this. Snapshots. After writing a test query I discovered that the standard edition of SQL Server does NOT support this. :-(

Given the fact that I want this to be automated: what are my options? Overwriting the data files immediately doesn't sound like a good option to me...

Some other information:

  • Windows 7
  • Microsoft SQL Server Standard Edition (64-bit), 10.50.1600.1
  • Database size is app. 1 gigabyte
  • This is only meant for testing. So the database is NOT used by other users when I'm returning to my 'snapshot'.
  • More information about snapshots:

Thank you in advance!

Answer

Ben picture Ben · Mar 14, 2011

Detach the MDF file, copy it somewhere, re-attach it.

When you want to reset the database, detatch the MDF, copy the old one over the top, and re-attach it.

Alternatively backup and restore.

Related questions

Should UAT data be a mirror of Production? And if so, how?

We have been kicking about an idea where UAT can be tested with near live data (say maximum a week old). I strongly believe that development and QA environments should be in control of their own data, but UAT (the …

Read More
Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have …

Read More
How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More