10

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sunib
  • 304
  • 2
  • 14

4 Answers4

6

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.

Ben
  • 34,935
  • 6
  • 74
  • 113
6

If you're doing this for testing purposes, you can use Developer Edition:

SQL Server 2008 Developer includes all of the functionality of Enterprise Edition, but is licensed only for development, test, and demo use.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Ok, that's good to know. In this case it's going to be difficult, I want the test situation to be exactly the same as the production environment. – Sunib Mar 15 '11 at 08:06
3

I homebrewed a solution like this because I wasn't happy with the way replication was running (and I was also using standard edition) - maybe my solution will lead you in the right direction:

http://trycatchfinally.net/2009/09/moving-a-sql-server-database-to-another-server-on-a-schedule-without-using-replication/

Basically, it takes a periodic backup, zips it, FTPs it somewhere, and the remote server checks for new files, extracts them, restores them, and then emails you to let you know a new replica has been restored.

SqlRyan
  • 33,116
  • 33
  • 114
  • 199
1

For testing, you can use Developers Edition, which has snapshots and is quite cheap. However, we prefer to build a new test database from scripts in source control - that allows us to easily determine the changes, quickly roll out a testing system on any box, and saves us a lot of time.

A-K
  • 16,804
  • 8
  • 54
  • 74