Today I took 30 minutes to save hours

UPDATE – 1/6/2011 : Since writing this post I have bundled this entire reset process into a single windows batch file. In addition to restoring my database, I also have to remove temp files from my server, so it was time to go command line with all of this.

Here the contents of my batch file.

### DELETE TEMP FILES
del /q /f /s Z:_FavoriteClient_GIT_webappinvoices* /f /s

### RESTORE DATABASE
sqlcmd -S "localhost" -i Z:_FavoriteClient_GIT_sqlrestoreBackup.sql

To use this in a .bat file, copy and paste the text into Notepad, then save as “restore.bat”.  Remember, you will need to wrap your filename.bat with quotes, otherwise Notepad will save the file as filename.bat.txt.

* The contents of restoreBackup.sql is listed below.

---

One of my favorite pastimes as a programmer is writing code that writes code, aka workflow automation.  It’s not something I do on every project, but I’ve been doing a lot of SQL Server development recently, and I’ve been having a lot of fun using the INFORMATION_SCHEMA views to build VO classes, forms representing tables, or just finding what tables have a specific column.  Today I took a 30 minute trip to the SYS side and wrote a tsql script that will save me hours.

The web application I’m working on is a payment processor made up of five steps.  In order to test I have to setup multiple sales for multiple clients, then log in as three different admin users to push the transactions through the system.  This gets my test transactions to the proper testable state.  The process of setting up testable transactions takes over 30 clicks, and once I hit step 3 of the wizard, my test transactions are completed in a way that I have to re-setup the test data ( 20 GOTO 10 ).

I thought about a handful of options, and ended up going with this solution.

1. Setup all test transactions in web application by hand ( get the data ready )
2. Take a full database backup ( freeze the data )
3. Use TSQL script to drop all connections to my app’s db, then restore the database to the “testable” state from step 1 ( reset the DB )

Here is my TSQL script

[sql]
DECLARE @sessID int,
@dbName varchar(50),
@userName varchar(50),
@backupFile varchar(200)

SET @dbName = ‘DA413’ — your database name
SET @userName = ‘DA413’ — sql user account to look for
SET @backupFile = ‘D:DBBackupDA413.bak’ — path to SQL backup file

— use a cursor to store all session_ids
DECLARE session_cursor CURSOR
FOR
SELECT session_id
FROM sys.dm_exec_sessions
WHERE original_login_name = @userName

— open cursor and grab first row
OPEN session_cursor
FETCH NEXT FROM session_cursor INTO @sessID

— loop through session_ids
WHILE @@FETCH_STATUS = 0
BEGIN

— kill it
— using EXEC because the sproc kill does not like @variables
EXEC(‘kill ‘ + @sessID)

— get the next session_id
FETCH NEXT FROM session_cursor INTO @sessID
END

— cursor cleanup
CLOSE session_cursor
DEALLOCATE session_cursor

— restore backup
USE master
RESTORE DATABASE DA413
FROM DISK = @backupFile
GO
[/sql]

A few notes about this script :

• I only need to disconnect my web application’s database user, not any user
• I’m using the latest full backup, and not a specific database snapshot
• The SPROC kill doesn’t like @variables as input, so use EXEC
• Your web server doesn’t know the user was disconnected, so you’ll have to log back into your application.
• If you use this technique, be sure to RERUN your backup if you add anything to the database ( EX : new table column, stored procedure, etc )
• If you’re not the only person connected to this database, make sure you don’t disconnect anybody else using the same database name

This solution is perfect for me because I have full control over my code, database, and server.  It’s also great because I can test my application, run a single sql script, and 10 seconds later I can test my application again.  While this solution is perfect for me, it’s probably best used as reference for others.  However, this technique of rolling back the database could be applied to any software application using SQL Server for it’s datasource.

Hope this helps somebody.