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.

Copy a MySQL database to your local MySQL machine

Here’s one way to copy a MySQL database from one server to another using phpMyAdmin, and the MySQL command line client.

Requirements :

• MySQL 5.x installed on your local machine
• MySQL command line client on your local machine
• Access to phpMyAdmin on your source MySQL machine

Assumptions:

• You have access to the MySQL command line client.
• You have the root password for your local MySQL instance
• You are comfortable using phpMyAdmin and MySQL command line client

Instructions:

1. Log into phpMyAdmin on your source MySQL machine.
2. Click the Export link on the lower part of the home page.
3. Select your database in the ‘Export’ box on the left.
4. Make sure SQL is selected under the database box.
5. In the ‘Options’ box on the right, make sure Structure and Data are selected.
6. Make sure to check ‘Add DROP TABLE / DROP VIEW’ as well.
7. Towards the bottom, check the box next to ‘Save as file’. This will be the file we import later.
8. Click the GO button, and your browser should prompt you to download your SQL file. *Make note of where you saved your exported sql file. I saved mine to d:downloadsimport.sql because it’s an easy path to use later.
9. Open your local MySQL Command Line Client. ( From MySql Program menu or type “mysql -u root -p” in the run dialog box ).
10. Enter your root password when prompted.
11. Import your database by running this command “mysql . d:downloadsimport.sql“. Be sure to adjust the path to match where you saved your sql file from step 8.
12. Now you should see your database listed when you run the command “show databases;“

 

Here are instructions for doing the same thing on OS X

1. Follow steps 1 through 8 above to created your sql export file
2. Connect to mysql “/usr/local/mysql/bin/mysql -u root -p”
3. {enter root password}
4. If it’s not already created, created your databse “create database {your db name};”
5. Select your database “user {your db name};”
6. “mysql . /users/ericfickes/Downloads/mydbfile.sql”

Be sure to adjust the paths listed to match your MySQL install, and the location where you downloaded the sql file.