Unit testing database

I want to test my database as part of a set of integration tests. I've got all my code unit tested against mocks etc for speed but I need to make sure all the stored procedures and code is working as it should when persisting. I did some Googling yesterday and found a nice article here http://msdn.microsoft.com/en-us/magazine/cc163772.aspx but it se开发者_运维技巧emed a little old. I wondered if there is any current 'better' way of clearing out the database, restoring to an expected state or rolling back ready for each test? I'm coding in c#4, mvc3 using sql 2008.

We are using DbUnit to set up and/or tear down the database between tests as well as to assert database state during test.

It's stupid-simple, so it may not be exactly what you need, but what I've done is keep a backup of the database at a given sane state - usually what the current production database is it. Then, for each build we restore that database (using Jenkins, NANT and SQLCMD), apply the current builds update scripts and run our test suite. This has the advantage of both giving you a database that is a 'known quantity' and it verifies that your upgrade scripting is working.