Instantly share code, notes, and snippets.

Embed
What would you like to do?
Set of batch & sql scripts for rebuilding a sql server db. Start at rebuild*.bat & upgrade-live.bat

We've created a set of good old fashioned windows batch scripts which tie together the process of completely rebuilding test systems and their databases. This gives us confidence that when we eventually upgrade a live web system that all the pieces of the upgrade have been well tested. The scripts will take an ASP.NET website offline, upgrade it (code and database) and put it back online all in one script. They can be run with or without a continuous integration server.

You can find the files here: https://gist.github.com/3164291

To rebuild a local development system run rebuild.bat, to rebuild a QA system run rebuild-QA.bat which overrides some of the values in the main rebuild file.

Some more details:

  • The scripts must be run in a "Visual Studio Command Prompt" for the code build & deployment to work.
  • The program relies on SqlHawk to do the database upgrade which must be available on the path.
  • You can set environment variables at the command prompt to alter settings without altering the script files.
  • Build and deploy of the code depends on visual studio due to the publish & web config transform parts.

The scripts will reset to a snapshot or backup of live before applying changes, meaning you can alter any part of your scripted update and retest all the changes.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Application Offline</title>
<style type="text/css">
div {
background-color:#ffffcc;
padding-top:10px;
padding-bottom:10px;
padding-left:10px;
padding-right:10px;
border-style:solid;
border-color:Black;
border-width:1px;
}
</style>
</head>
<body>
<div>
This application is currently offline. Site update in progress.
</div>
</body>
</html>
<!-- http://stackoverflow.com/questions/2905151/msbuild-script-and-vs2010-publish-apply-web-config-transform -->
<!-- this is an msbuild configuration for applying web config transforms -->
<Project ToolsVersion="4.0"
DefaultTargets="Transform"
xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<UsingTask TaskName="TransformXml"
AssemblyFile="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\Web\Microsoft.Web.Publishing.Tasks.dll"/>
<PropertyGroup>
<ProjectPath>..\Shell.DocumentGenerator.Mvc</ProjectPath>
<TransformInputFile>$(ProjectPath)\Web.config</TransformInputFile>
<TransformFile>$(ProjectPath)\Web.$(Configuration).config</TransformFile>
<TransformOutputFile>$(DeployPath)\Web.config</TransformOutputFile>
<StackTraceEnabled>False</StackTraceEnabled>
</PropertyGroup>
<Target Name="Transform">
<TransformXml Source="$(TransformInputFile)"
Transform="$(TransformFile)"
Destination="$(TransformOutputFile)"
StackTrace="$(StackTraceEnabled)" />
</Target>
</Project>
/* script for taking a snapshot of a test db for testing scripts */
-- This is manually run for setting a point for the reset scripts to go back to before re-applying all outstanding changes. Replace ##database## with the name of the database you are creating
-- ref http://blog.sqlauthority.com/2010/04/05/sql-server-2008-introduction-to-snapshot-database-restore-from-snapshot/
-- create snapshot of CI db for testing scripts
create database ##database##_snapshot
on (name = 'DevDb', -- name of source file
filename = 'C:\sqlserver_databases\snapshots\##database##_snapshot.ss1')
as snapshot of ##database##
-- Tim Abell
-- 3 May 2012
-- Set up config stored in database for local testing.
/*
Note that $(mailbox) is a variable set for sqlcmd.
ref: http://msdn.microsoft.com/en-us/library/ms188714.aspx
As the exchange server is part of our domain the logged in user will
have permissions to send as the matching email address.
*/
update globalConfig
set value = '$(mailbox)'
where name like '%EMAIL_ADDRESS';
update globalConfig
set value = REPLACE(value, 'http://www.example.com', '$(siteUrl)')
where value like 'http://www.example.com%';
@echo off
Setlocal EnableDelayedExpansion
REM set variables for building QA
set server=staging
set database=QA
if "!batch!"=="" set batch=!USERNAME! on !COMPUTERNAME!; Manual run. Changeset Cxxxx
set publishPath=\\staging\web\QA
set mailbox=shared@mycompany.co.uk
set siteUrl=http://staging/qa
set resetType=snapshot
set config=Debug (QA)
call rebuild.bat
@echo off
REM =============================================================================================
REM !! DESTRUCTIVE !!
REM Resets a test database, runs upgrades, adds test data, adds test files.
REM Must be run in visual studio command prompt to allow build.
REM Requires SqlHawk, msbuild, and msdeploy (part of webdeploy) to be on the path.
REM Environment variables can be overridden by caller / calling scripts.
REM It is recommended that you set a buffer of 3000 lines on your command window to avoid losing output.
REM =============================================================================================
REM allow use of !var! everywhere, the normal !var! version doesn't work with if / for etc.
REM and avoid messing with variables in the command window scope:
Setlocal EnableDelayedExpansion
set localPublishPath=..\WebProject
REM set variable defaults if not already set by caller
if "!server!"=="" set server=localhost
if "!database!"=="" set database=DevDb
if "!batch!"=="" set batch=!USERNAME! on !COMPUTERNAME!; Manual run
if "!publishPath!"=="" set publishPath=!localPublishPath!
if "!mailbox!"=="" set mailbox=!USERNAME!@mycompany.co.uk
if "!siteUrl!"=="" set siteUrl=http://localhost:7031
if "!resetType!"=="" set resetType=backup
if "!config!"=="" set config=Debug
echo Server : !server!
echo Database : !database!
echo Batch : !batch!
echo Publish path: !publishPath!
echo Mailbox : !mailbox!
echo Site url : !siteUrl!
echo Config : !config!
echo.
echo !date! !time! Starting rebuild...
if not "!publishPath!"=="!localPublishPath!" (
echo.
@echo Taking app offline...
copy app_offline.htm "!publishPath!"
if !errorlevel! neq 0 exit /b !errorlevel!
)
if not "!skipDb!"=="" (
echo.
@echo Skipping db rebuild.
) else (
echo.
@echo Reverting to clean backup of live db...
@echo Do not interrupt this step as a broken connection can wedge the database. Ref http://stackoverflow.com/questions/520967
sqlcmd -b -S !server! -E -d master -i reset-to-!resetType!.sql -v database=!database!
if !errorlevel! neq 0 exit /b !errorlevel!
)
REM this is the bit that will be done on the final deployment to live:
echo.
@echo Running upgrade...
call upgrade.bat
if !errorlevel! neq 0 exit /b !errorlevel!
if "!skipDb!"=="" (
echo.
@echo Applying test data...
pushd .
cd test-data\sql\
call test-data.bat
if !errorlevel! neq 0 exit /b !errorlevel!
popd
echo.
@echo Configuring global config...
sqlcmd -b -S !server! -E -d !database! -i global-config.sql -v mailbox="!mailbox!" siteUrl="!siteUrl!"
)
echo.
@echo Copying test files into publish directory...
xcopy /S /Y test-data\upload !publishPath!\data\upload\
if !errorlevel! neq 0 exit /b !errorlevel!
if not "!publishPath!"=="!localPublishPath!" (
echo.
@echo Putting app back online...
del !publishPath!\app_offline.htm
)
echo.
@echo !date! !time! Done.
-- !! DESTRUCTIVE !!
-- Restore a local database from backup.
-- (sqlexpress doesn't support snapshots)
-- To be used in preparation for re-testing scripted changes with sqlhawk
-- This script is not intended to be run manually. See ../README.txt for the (re)build process.
-- kill active connections
-- ref http://www.julian-kuiters.id.au/article.php/sql-server-2005-snippet-drop-db-conn
ALTER DATABASE [DevDb]
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [DevDb]
SET ONLINE
RESTORE DATABASE [DevDb]
FROM DISK =
N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\DevDb.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
use [DevDb]
GO
-- fix login mapping after databse restore
alter user WebsiteUser with login = WebsiteUser, default_schema = dbo
-- Tim Abell
-- 23 May 2012
-- !! DESTRUCTIVE !!
-- Restore a local database back to the last snapshot.
-- To be used in preparation for re-testing scripted changes with sqlhawk
-- This script is not intended to be run manually. See ../README.txt for the (re)build process.
-- ref http://blog.sqlauthority.com/2010/04/05/sql-server-2008-introduction-to-snapshot-database-restore-from-snapshot/
/*
Note that $(database) is a variable set for sqlcmd.
ref: http://msdn.microsoft.com/en-us/library/ms188714.aspx
*/
-- kill active connections
-- ref http://www.julian-kuiters.id.au/article.php/sql-server-2005-snippet-drop-db-conn
ALTER DATABASE $(database)
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE $(database)
SET ONLINE
-- reset the snapshot
restore database $(database)
from database_snapshot = '$(database)_snapshot';
-- fix login mapping after databse restore
use $(database)
alter user WebsiteUser with login = WebsiteUser, default_schema = dbo
@echo off
REM this is a generic script for running all the files matching pattern test-data_*.sql in a folder
rem set DelayedExpansion to be able to catch errorlevel inside a loop
setlocal EnableDelayedExpansion
for /R %%I IN (test-data_*.sql) DO (
@echo %%I
sqlcmd -b -S !server! -E -d !database! -i "%%I"
if !errorlevel! neq 0 exit /b !errorlevel!
)
@echo off
Setlocal EnableDelayedExpansion
REM TODO: Fill this in with the correct values for live
set server=liveSql
set database=liveDb
if "!batch!"=="" set batch=!USERNAME! on !COMPUTERNAME!;
set publishPath=\\web01\web\live
set config=Release (Production)
echo Server : !server!
echo Database : !database!
echo Batch : !batch!
echo Publish path: !publishPath!
echo Config : !config!
echo.
REM saftey buffer, remove this if we move to a proper build server based deployment
@echo Are you sure you want to upgrade the live db?
@echo * Have you made a backup first?
@echo * Have you filled in the parameters of this script?
@echo.
@echo Press Ctrl+C to cancel, any other key to continue.
pause >nul
@echo Taking app offline
copy app_offline.htm !publishPath!
echo.
@echo Running upgrade...
call upgrade.bat
if !errorlevel! neq 0 exit /b !errorlevel!
@echo Putting app back online...
del !publishPath!\app_offline.htm
@echo Done.
@echo off
Setlocal EnableDelayedExpansion
REM This script takes the live database or a clone of it, and upgrades it to the latest version based on the stored procedures and scripts in source control.
REM The current live database (and therefore the backups and snapshots) have never had SqlHawk run, thus the tracking table needs to be created. Once live has the tracking table the initialize-tracking step must be discarded.
echo.
echo !date! !time! Starting upgrade...
if "!skipDb!"=="" (
echo.
@echo Initializing upgrade log table...
java -jar "C:\Program Files\SqlHawk\sqlhawk.jar" ^
--initialize-tracking -t mssql05-jtds -h !server! --sso -d !database! -s dbo ^
--driver-path "C:\Program Files\jtds-1.2.5\jtds-1.2.5.jar"
if !errorlevel! neq 0 exit /b !errorlevel!
echo.
@echo Upgrading database...
java -jar "C:\Program Files\SqlHawk\sqlhawk.jar" ^
--scm-input --target-path ..\database\ ^
--database-output -t mssql05-jtds -h !server! --sso -d !database! -s dbo ^
--driver-path "C:\Program Files\jtds-1.2.5\jtds-1.2.5.jar" ^
--upgrade-batch "!batch!"
if !errorlevel! neq 0 exit /b !errorlevel!
REM "sleep" for windows. hack. http://stackoverflow.com/questions/7958265/sleep-command-in-batch-file
REM this is because on a local SQLExpress the next sql command fails with spurious login failure if it is run too soon.
@echo Waiting for database to settle...
ping -n 10 127.0.0.1 > NUL
if !errorlevel! neq 0 exit /b !errorlevel!
)
REM Only publish if not working locally.
if not "!publishPath!"=="!localPublishPath!" (
REM move to sln directory ready for build and publish
pushd .
cd ..\
set packageTemp=obj\PackageTemp
echo.
@echo Building and packaging the code...
REM delete old copy
rd /S /Q "WebProject\!packageTemp!"
msbuild /p:DeployOnBuild=true;DeployTarget=PipelinePreDeployCopyAllFilesToOneFolder;PackageTempRootDir="!packageTemp!";AutoParameterizationWebConfigConnectionStrings=false;Configuration="!config!"
if !errorlevel! neq 0 exit /b !errorlevel!
echo.
@echo Publishing code ...
REM Cleanup
REM Delete outdated folders
for /d %%a in ("!publishPath!\*") do (
rd /S /Q %%~Fa
if !errorlevel! neq 0 exit /b !errorlevel!
)
REM delete outdated files except the offline file (ignore case on comparison as windows corrects the path)
for %%a in ("!publishPath!\*") do (
if /i not "%%~fa" == "!publishPath!\app_offline.htm" (
del /Q "%%~fa"
if !errorlevel! neq 0 exit /b !errorlevel!
)
)
REM deploy updated copy
xcopy /S /Y "WebProject\!packageTemp!\PackageTmp\*" "!publishPath!"
if !errorlevel! neq 0 exit /b !errorlevel!
REM delete temp files
rd /S /Q "WebProject\!packageTemp!"
popd
echo.
@echo Publishing Web.config for configuration...
msbuild config-transform.xml /p:Configuration="!config!";DeployPath="!publishPath!"
if !errorlevel! neq 0 exit /b !errorlevel!
)
echo.
echo !date! !time! Upgrade complete.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment