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.