Doing test driven development often requires you to reset your database to a well-defined state. For this purpose I’ve come up with the following T-SQL that first clears all outstanding connections to the database, enabling the restore, then restores it from its backup set. Note that ‘WITH FILE=2’ points to the second backup set in my backup file, allowing me to choose from different bases (sets) in the same backup file.
— Kill all current connections
DECLARE @sqlcmd VARCHAR(8000);
SELECT @sqlcmd=COALESCE(@sqlcmd,”)+‘KILL ‘+CAST(spid AS VARCHAR(10))+‘; ‘
WHERE DBID=DB_ID(‘OEDB’) AND HOSTNAME <>”
— Restore test-data
Of course you can execute this automatically from your test code – using SMO (Systems Management Objects) is another option for doing a programmatic restore. I’ve experimented with SMO, but the scripting seems so much easier.