Backup Visual SVN Repositories

(using svnadmin dump)

After migrating from a Linux SVN server to a Windows server with Visual SVN, I needed a new backup script.  Visual SVN doesn't come with a backup solution, but it does have enough SVN admin tools to be able to complete this task without having to install any additional SVN tools. 

The scripts perform an svadmin dump on every repository on the server, then zip those dump files into a one zip file with the current date, using 7-zip.  I used 2 scripts because I wanted to get as much logging as possible into the final zip file. 

You’ll find loads of similar scripts throughout the web, the ones below are just my variant of other methods found; please feel free to cherry pick anything you need out of here.  It goes without saying that you'll have to customize each script to get it work on your system.  This code is free and comes with no guarantees.    Good luck!

Code Highlights:
These are conventions I felt were interesting, I've removed some of the script particulars:

Call svn_dump_repos.cmd > log.txt

• Calls the dump script and pipes the output to a text file (log_timestamp.txt)

Dir /A:D /B> dirs.txt

•  (Run in Repository folder) Stores a list of all folders (repositories) into a text file (dirs.txt)

FOR /F %%r IN (dirs.txt) DO ( ... )

• Runs a loop for each item (%%r) in dirs.txt (i.e. each repository)

I highlighted the same lines that I felt were important in the code below.

Script 1:
svn_backup.cmd

@ECHO OFF
REM svn_backup.cmd
REM 02/10/2012 jmj
REM
REM This script will log the entire script: svn_dump_repos.cmd, and add it to the final zip file.
REM Instead of providing command-line arguments, each variable is set below.
REM

CLS

REM cpuname used for filename, workingdir for holding dumps/logs/etc,
REM and destdir for final destination
set cpuname=svnserver
set workingdir=D:\svn_backup\temp
set destdir=\\backup_server\DEV

REM timestamp = YYYYMMDD
set timestamp=%DATE:~-4%%DATE:~4,2%%DATE:~7,2%

REM Program file locations
set zipexe="C:\Program Files\7-Zip\7z.exe"


echo.
echo ****************************************************
echo Starting svn_dump_repos.cmd, (log as log_timestamp.txt)
echo ****************************************************
if not exist %workingdir% md %workingdir%
call svn_dump_repos.cmd > %workingdir%\log_%timestamp%.txt


REM svn_dump_repos.cmd created the final zip file, add any log files into it (*.txt)
echo.
echo ****************************************************
echo Adding log files to zip file
echo ****************************************************
cd %workingdir%
%zipexe% a %cpuname%_%timestamp%.zip %workingdir%\*.txt

echo.
echo ****************************************************
echo Move archive to final destination
echo ****************************************************
move *.zip %destdir%

echo.
echo ****************************************************
echo Removing the temporary files and exit
echo ****************************************************
del /f /q %workingdir%\*.txt
del /f /q %workingdir%\*.svndump

Script 2:
svn_dump_repos.cmd

@ECHO OFF
REM svn_dump_repos.cmd
REM 02/10/2012 jmj
REM
REM This script will perform a svnadmin dump on each repository in a folder
REM
REM Instead of providing command-line arguments, each variable is set below.
REM

REM cpuname used for filename, workingdir for holding dumps/logs/etc,
REM and destdir for final destination (not used here, but you want to include location in log)
set cpuname=svnserver
set repo=D:\Repositories
set workingdir=D:\svn_backup\temp
set destdir=\\backup_server\DEV

REM timestamp = YYYYMMDD
set timestamp=%DATE:~-4%%DATE:~4,2%%DATE:~7,2%

REM Program file locations
set svnadminexe="C:\Program Files\VisualSVN Server\bin\svnadmin"
set zipexe="C:\Program Files\7-Zip\7z.exe"


echo.
echo ****************************************************
echo Hello! Ready! 
echo.
echo Create svn dump files for each repository on server
echo.
echo Using these Settings:
echo  - Computer Name: %cpuname%
echo  - Repository Folder: %repo%
echo  - Working Directory: %workingdir%
echo  - Destination Directory: %destdir%
echo  - Zip Exe (7-zip) Location: %zipexe%
echo  - svnadmin Location: %svnadminexe%
echo ****************************************************
echo.

echo.
echo ****************************************************
echo Dump Respositories (name_timestamp.svndump)
echo ****************************************************
cd %repo%
dir /A:D /B> %workingdir%\dirs.txt
cd %workingdir%

FOR /F %%r IN (%workingdir%\dirs.txt) DO (
    echo Dumping: %%r
    %svnadminexe% dump %repo%\%%r > %workingdir%\%%r_%timestamp%.svndump
    if errorlevel 1 then Goto Error
)

echo.
echo ****************************************************
echo Compressing Dump files (servername_timestamp.zip)
echo ****************************************************
%zipexe% a -tzip %workingdir%\%cpuname%_%timestamp%.zip %workingdir%\*.svndump

if errorlevel 1 then Goto Error

echo.
echo ****************************************************
echo testing the new archive
echo ****************************************************
%zipexe% t %workingdir%\%cpuname%_%timestamp%.zip

if errorlevel 1 then Goto Error

echo.
echo ****************************************************
echo listing contents of new archive
echo ****************************************************
%zipexe% l %workingdir%\%cpuname%_%timestamp%.zip

if errorlevel 1 then Goto Error

GOTO Finish

:Error
echo.
echo There was an Error in the process
echo Please double-check the results!
echo.
echo GoodBye!
@ECHO ON

:Finish
echo.
echo Finished!

@ECHO ON

Scripting Database Objects from MS SQL Server 2000 to 2005 for Subversion

Our development team keeps track of Database objects in our environment by scripting changes from SQL Server to a text file, then using a Subversion repository to keep track of changes. The process works pretty well for our team size, and Subversion provides us with a fairly good history of each database object.

We started this process back in SQL Server 2000 using the scripting utilities provided to generate the text files, using their file name convention. However, after we upgraded to SQL Server 2005, Microsoft changed the file naming convention of the text files. I’ve included a small table showing some of the differences.

Database Object	SQL 2000	SQL 2005
View	dbo.ViewName.VIW	dbo.ViewName.View.sql
Table	dbo.TableName.TAB	dbo.TableName.Table.sql
Table Trigger	dbo.TriggerName.TRG	TriggerName.Trigger.sql
Stored Procedure	dbo.StoredProcName.PRC	dbo.StoredProcName.StoredProcedure.sql

The filename change was a big thorn in our side. Subversion was tracking the files by filename and now all the filenames were going to change! At first we toyed with the idea of writing our own code to export the objects using the old naming convention, but we didn’t want to have to maintain yet another program. In the end we decided we were just going to have Subversion rename all the files accordingly.

Since we’re mostly doing our development on Windows, we use TortoiseSVN (http://tortoisesvn.tigris.org/), which is a great tool. However, we had around 600+ script files to rename and I didn’t feel like individually renaming each. Instead, I decided to write a small program to break down each file, determine what type of object it was, and then call “svn rename” with the appropriate change.

To do this, I had to download the Subversion client tools for Windows (http://subversion.tigris.org/project_packages.html). After I had it installed and the command line tools were working, I wrote a small C# program in Visual Studio to do the work. I never formally finished the program; instead I simply used it in debug mode so that I could step through to watch the process in detail (It only had to work once).

The end result was a bunch of renamed script files that had their revision history kept intact. I’m not sure if this was the best way to go about solving the problem, but it worked. I thought my code and comments might be useful for someone else who has a similar problem. This code is free and comes with no guarantees. Good luck.

Download the code here

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Text;
using System.IO;


namespace RenameSVNFiles
{
class Program
{
static void Main(string[] args)
{

Console.WriteLine("Begin SVN Rename Script");

//Set to your directory
String sDir = "D:/Projects/dbscripts";

DirectoryInfo sourceFiles = new DirectoryInfo(sDir);
FileInfo[] destFiles = sourceFiles.GetFiles("*.*");

String sOrigFileName = "";
String sNewFileName = "";
String strCmdLine = "";
String sOrigFileExt = "";
int iFileExtLength = 0;
int iModifiedCount = 0;

//Declare and instantiate a new process component.
System.Diagnostics.Process process1 = new System.Diagnostics.Process();
process1.StartInfo.WorkingDirectory = sDir;

foreach (FileInfo fi in destFiles)
{

sOrigFileName = fi.Name;

//Find extension length and File name (assumes syntax of filename.extension)
iFileExtLength = sOrigFileName.Length - sOrigFileName.LastIndexOf(".");
sOrigFileExt = sOrigFileName.Substring(sOrigFileName.LastIndexOf("."), iFileExtLength);

//change each file's extention to the new naming standard
switch(sOrigFileExt)
{
case ".VIW":
sNewFileName = sOrigFileName.Substring(0, sOrigFileName.LastIndexOf(".")) + ".View.sql";
iModifiedCount++;
break;
case ".TAB":
sNewFileName = sOrigFileName.Substring(0, sOrigFileName.LastIndexOf(".")) + ".Table.sql";
iModifiedCount++;
break;
case ".TRG":
sNewFileName = sOrigFileName.Substring(0, sOrigFileName.LastIndexOf(".")) + ".Trigger.sql";

//Triggers do not have the "dbo." at the beginning of the file anymore - remove them
String tmpStr = "dbo.";
char[] trimChars = tmpStr.ToCharArray();
sNewFileName = sNewFileName.TrimStart(trimChars);
iModifiedCount++;
break;
case ".PRC":
sNewFileName = sOrigFileName.Substring(0, sOrigFileName.LastIndexOf(".")) + ".StoredProcedure.sql";
iModifiedCount++;
break;
default:
//default - skip file
continue;
}

strCmdLine = "rename " + sOrigFileName + " " + sNewFileName;

try
{
process1.StartInfo.FileName = "svn.exe";
process1.StartInfo.Arguments = strCmdLine;
process1.StartInfo.CreateNoWindow = true;
process1.Start();
process1.WaitForExit();
}
catch (Exception ex)
{
Console.WriteLine(
"Exception Occurred :{0},{1}",
ex.Message, ex.StackTrace.ToString()
);
Console.WriteLine("While trying: " + sOrigFileName + " -> " + sNewFileName);
}

Console.WriteLine(sOrigFileName + " -> " + sNewFileName);
}


process1.Close();

Console.WriteLine("");
Console.WriteLine("");
Console.WriteLine("Number of files in directory: " + destFiles.GetLength(0));
Console.WriteLine("Number of files modified: " + iModifiedCount);
Console.WriteLine("Press any key to exit...");
Console.ReadKey();
}
}
}