Batch files to check SQL 2005 (MDF) files in and out of Subversion source control - Jon Galloway

Batch files to check SQL 2005 (MDF) files in and out of Subversion source control

Here are a few batch files I whipped up to help our team keep development databases in sync with our Subversion repository. These handle detach / reattach from the database, so running the checkout script has us the latest version in the repository in twenty seconds.

These scripts rely on 7-zip to compress / decompress the MDF file to speed up the checkin / checkout. You can download 7-zip here, or you can modify the scripts if you want to use another commandline compression program.

There are a few things you're going to have to fill in (marked by ***FILL-IN***), but I've done my best to fill in what I can - for instance, I'm defaulting the data directory based on the SQL 2005 install directory in the registry. You can override any of the variables if your data directory's on another drive or something.

1. Update from SVN, unzip, and attach to database

@ECHO OFF
::Name of database you're connecting to
SET DATABASENAME=***FILL-IN***
::Database instance
SET SERVER=%COMPUTERNAME%\***FILL-IN***
::Path to your subversion repository
SET SVNPATH=svn://***FILL-IN***
SET WORKINGDIRECTORY=%~dp0
::Set the directory you'll be checking the MDF file out to. Defaults to current directory.
SET WORKINGDIRECTORY=C:\Projects\***FILL-IN***\Trunk\
SET DATADIRECTORY=C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data
FOR /F "tokens=2* delims= " %%A IN ('REG QUERY "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQL2005\Setup" /v SQLPath') DO SET DATADIRECTORY=%%B\Data

TITLE Checking out latest version of %DATABASENAME% database
ECHO Updating from source control
PUSHD %WORKINGDIRECTORY%
SVN update
"%SVNPATH%/%DATABASENAME%.mdf.zip"

ECHO Decompressing database (MDF) file
"%PROGRAMFILES%\7-zip\7z.exe" e -y %DATABASENAME%.mdf.zip

ECHO Detaching database
osql -E -S %SERVER% -d master -Q
"alter database [%DATABASENAME%] set single_user with rollback immediate"
osql -E -S %SERVER% -d master -Q
"sp_detach_db '%DATABASENAME%'"

ECHO Copying database (MDF) file
move "%WORKINGDIRECTORY%\%DATABASENAME%.mdf" "%DATADIRECTORY%\%DATABASENAME%.mdf"

ECHO Reattaching the database
osql -E -S %SERVER% -d master -Q
"sp_attach_single_file_db '%DATABASENAME%', '%DATADIRECTORY%\%DATABASENAME%.mdf'"
osql -E -S %SERVER% -d master -Q
"alter database [%DATABASENAME%] set multi_user"

ECHO Done
POPD

 

2. Detach from database, zip, check in to SVN, and reattach to database

@ECHO OFF
::Name of database you're connecting to
SET DATABASENAME=***FILL-IN***
::Database instance
SET SERVER=%COMPUTERNAME%\***FILL-IN***
::Path to your subversion repository
SET SVNPATH=svn://***FILL-IN***
SET WORKINGDIRECTORY=%~dp0
::Set the directory you'll be checking the MDF file out to. Defaults to current directory.
SET WORKINGDIRECTORY=C:\Projects\***FILL-IN***\Trunk\
SET DATADIRECTORY=C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data
FOR /F "tokens=2* delims= " %%A IN ('REG QUERY "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQL2005\Setup" /v SQLPath') DO SET DATADIRECTORY=%%B\Data

TITLE Checking out latest version of %DATABASENAME% database
ECHO Detaching database
PUSHD %WORKINGDIRECTORY%
osql -E -S %SERVER% -d master -Q
"alter database [%DATABASENAME%] set single_user with rollback immediate"
osql -E -S %SERVER% -d master -Q
"DBCC SHRINKDATABASE (%DATABASENAME%)"
osql -E -S %SERVER% -d master -Q
"sp_detach_db '%DATABASENAME%'"

ECHO Copying database (MDF) file
copy "%DATADIRECTORY%\%DATABASENAME%.mdf" "%~dp0"

ECHO Reattachind database
osql -E -S %SERVER% -d master -Q
"sp_attach_single_file_db '%DATABASENAME%', '%DATADIRECTORY%\%DATABASENAME%.mdf'"
osql -E -S %SERVER% -d master -Q
"alter database [%DATABASENAME%] set multi_user"

ECHO Compressing database (MDF) file
"%PROGRAMFILES%\7-zip\7z.exe" u -tzip %DATABASENAME%.mdf.zip %DATABASENAME%.mdf
del %DATABASENAME%.mdf

ECHO Checking in to source control
svn add %DATABASENAME%
.mdf.zip
svn commit %DATABASENAME%
.mdf.zip -m "Automatic check-in"

ECHO Done
POPD

 

Yes, you could do this with Powershell, too. That's not what I did.

Published Saturday, October 28, 2006 9:49 PM by Jon Galloway
Filed under: ,

Comments

# re: Batch files to check SQL 2005 (MDF) files in and out of Subversion source control

I don't know what is going wrong, but I see the following behavior. When the database is detached no errors are shown.

However when the copy database starts it complains that the file is not found. When I check in the directory there was indeed no mdf file created. How is this possible?

Thursday, May 8, 2008 4:58 AM by Bas Greydanus

# re: Batch files to check SQL 2005 (MDF) files in and out of Subversion source control

How can I detach a databases if I´ve a text file with the names of the databases, in this text file exists like 30 names of databases to be detached..

It is posible to call in a variable an external file (text  file) and detach many databases in the same script ?

Sorry for my english, is not as good as I want

Friday, September 19, 2008 7:38 PM by Manuel