25
Mar
08

SQL Server Change Management: The Low-Tech Approach

Dealing with database changes can really be a pain in the ass. Here’s one exceedingly low-tech solution that I am using.

On my current project (SQL Server, using SQLExpress for local development), we store a backup from the live database in a zip file in SVN. We store an actual live backup, but for another project I might scrub most of the data, depending on the size of the database and the nature of the personal info involved.

We name our SQL scripts for a particular release so that when sorted by file name, they execute in the correct order:

000_Create_New_Totally_Killer_Table.sql

001_Add_That_Column_We_Forgot_In_The_Last_Release.sql

Note that on SQL Server (and probably other RDBMSes as well), many scripts are order-independent (sprocs). Even so, many times it is easiest to deal with the migration as a series of deltas that execute in order.

We also store an SQL file that creates an empty database and user. I generated this by using SQL Management Studio to script my changes, and then deleting the cruft from the resultant script, so that it can run on SQL2005 and SQL2000.

We have a batch file like the following that any developer can run that will create a clean local copy of the database with all scripts applied.

This also is used to verify that script changes/additions are working before commiting to SVN.

It’s pretty low-tech but it works so far.

REM pass in the server name (for ex localhost\SQLEXPRESS)

REM unzip .bak backup from zip file

7za e Live_Database_Backup.zip -y

REM script that creates empty database, user, etc.

REM this SQL file can be created by hand or from Management Studio

type Create_Local_DB_And_Restore_From_live_Backup.sql >> tmp.sql

REM create monster script by concatenating in order of file name

REM we name them 000_foo.sql, 001_bar.sql etc

for /f %%G in (‘dir /b /o:n Path\To\Scripts\*.sql’) do type Path\To\Scripts\%%G >> tmp.sql

REM execute the monster script (uses windows auth)

osql -S %1 -d master -E -i tmp.sql

REM so we can examine tmp.sql if anything went wrong

pause

del Live_Database_Backup.bak

del tmp.sql

Advertisements

0 Responses to “SQL Server Change Management: The Low-Tech Approach”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: