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.

Weird, that bat file path doesn’t jive from the toolbar

I just noticed something when running some batch files on Windows 7. If I launch the file from Windows Explorer, the path in the command window matches the location of the batch file.

The paths, they match!

However, I typically launch my batch files from a toolbar on my taskbar that points to the same folder.  The batch file still works, but the path shown in the command window is weird.  I have no idea how an Adobe Version Cue path could get injected, but it does.

Weird paths

To capture both shots of the command window I had to hit Pause.  These aren’t faked, they’re just completely random.

XCOPY script maker utility

Here is an old utility project I started a few years ago to help make XCOPY scripts.  It’s an HTA ( HTML Application ) so it currently only runs in Internet Explorer.  Normally I wouldn’t release IE only code, but XCOPY is a windows only utility, and this is really just a quick and dirty little tool. Usage is simple :

1. Open with Internet Explorer
2. Point and click through the options
3. Click the ‘Make Script’ button

From here you can paste the xcopy script directly onto the command line, or into notepad and Save As “yourfile.bat”.

UPDATE: I can no longer upload the raw hta file to wordpress so I created xcopierHTA.pdf which contains the raw source code.  Copy and paste the code from this PDF into a text file and save as “xcopier.hta”.  Then run the HTA as normal in IE.

Download xcopierHTA.pdf

XCOPY maker in action