Source control database scripts

I’m very jealous to those lucky developers who are work under source-controlled  database environment.

It’s shameful to admit we are still working on one same test/develop database all the day, without automatic nightly refresh. Developers play on test db and override each other’s test data.  When things on test db really become ugly and out-of-date, one guy requests a refresh, then the same old story start over.

Local database develop idea is too new to most of us,  after months of effort management allow the new project can go with local db mode development. Which means, DBA has to present scripts instead of centrol shared database to developers.

This is a huge move to our database team, I understand they don’t want put / check-in their scripts into developers’ svn repository for now (too many new stuff come together, what? source control?) They feel comfortable just copying their work to the network drive. It can kind of work with continuous integration, but, no source control mean no history, no branch, no rollback…

I have to find a work around to this until those scripts can be checked into svn repository.

Here is our project build routine:

  1. db-init (run script from network drive, always latest)
  2. db-test
  3. code compile (from svn repository)
  4. code unit-test
  5. release (will move to artifact folder)

The first two step only exist in new project, my solution is to save every script to local drive, then zip them into db-release folder before 3, . In case developers wants to build a specific build, they can not run 1 and 2 anymore, because scripts might newer that code, they only need to unzip the zip file and run the separate db-init manually, then start from step 3.

An ugly solution, I know we don’t have to do this if we can get EVERYTHING including db scripts from source control repository. Someday it might happen to us.

Here are some NAnt trick I’ve used, including prefix the script with number to enable order the name.

  <target name="exec_SQL_as_SA">
     <echo message='executing ${osql.exe} ${sa.osql.ConnectionString} -b -i ${filename}  -v DBDIR="${DB.DIR}"...' />
    <property name="count" value="${int::parse(count)+1}" />
    <property name="padded_count" value="${string::pad-left(count, 3,'0')}"/>
    <copy file='${filename}'  tofile='${sql.release.dir}/sa/[${padded_count}]-${path::get-file-name(filename)}'/>

	  <exec program="${osql.exe}" failonerror="true">
	    <arg line='${sa.osql.ConnectionString} -b -i "${filename}" -v DBDIR="${DB.DIR}"'  />
	  </exec>
  </target>

<target name="run.db.init">
         ....
         <zip zipfile="${dir.release}\database_scripts.zip">
        <fileset basedir="${dir.sql}\release">
          <include name="**/*" />
        </fileset>
      </zip>
    </if>
  </target>

About deploy, it’s better to use batch instead of NAnt.

cd sa
rem if NOT Exist deploy.log (
@echo > deploy.log
rem )

for /f "delims=" %%a IN (‘dir /b *.sql’) do (
@echo executing %%~fa
@echo executing %%~fa >tmp.log
%SQLCMD% -S %SERVER% %AS_SA% -b -i "%%~fa" -v DBDIR=%DBDIR% -o tmp.log
if exist tmp.log copy deploy.log+tmp.log >NUL
)
if exist tmp.log del tmp.log >NUL
move deploy.log .. >NUL

cd ../user

@echo > testresults.txt
for /f "delims=" %%a IN (‘dir /b *.sql’) do (
@echo testing %%~fa
@echo testing %%~fa >temp.log
%SQLCMD% -S %SERVER% %AS_USER% -b -i "%%~fa" -v DBDIR=%DBDIR% -o tmp.log
copy testresults.txt+tmp.log >NUL
)
if exist tmp.log del tmp.log >NUL
move testresults.txt .. >NUL

cd ..

ref:http://www.computerhope.com/forhlp.htm

Advertisements

One thought on “Source control database scripts

  1. Have you tried Red Gate’s SQL Source Control, http://www.red-gate.com/products/sql_source_control? It allows you to source control your database schema objects in Subversion or Team Foundation Server directly from the Object Explorer in SQL Server Management Studio.

    It stores the scripts in source control in a format that SQL Compare can understand so that migration scripts can easily be created and it can all be automated into an automatic build or continuous integration process through SQL Compare’s command line.

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