Backing-Up a remote DB using Mercurial

I use a virtual server for a small web project. Basically it's just a content-site but still there is a database you have to backup somehow.

Lazy as I am, I wanted to centralize those backups and of course automate them: but using a virtual server, this is not the easiest task.

After some time, and figuring out that the MS Backup won't work for me, I had to find another solution.

Struggling with backuptools for a virtual server, I finally decided to implement my own solution. Therefore I misused my mercurial-server, which I already mentioned in another post before.

First of all, I needed to get a backup of the database itself. You can do this in the SQL Server Management Studio and then export the script. The script will somehow look like this:

BACKUP DATABASE [MyPreciousDatabase] TO  DISK = N'C:\backupfolder\backupfile.bak' WITH NOFORMAT, INIT,  NAME = N'Backupname', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

To call this script without opening the SQL-Manager again, you can simply write a .cmd or .bat file, which executes following command:

"c:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S [Servername]\[instance] -i "[path to the script mentioned before]"

What I have now, is a batch-file which creates me a backup of my database. But that's of course not enough: this backup has to be stored somewhere as well.

As mentioned before, I wanted this file to be pushed to my mercurial repository.

First of all, you need to create a repository where the DBBackup is stored, than create the repository on the server and configure them to work together by putting the serverpath to the hgrc or just configure it via TortoiseHG.

Then you need to commit the backup and push to the server repository. This works simply if you add following lines to the batch-file:

"c:\Program Files\TortoiseHg\hg.exe" add --cwd "c:\[BackupDirectory]"
"c:\Program Files\TortoiseHg\hg.exe" commit -m "Backup of my DB %date% %time%" --cwd "c:\[BackupDirectory]"
"c:\Program Files\TortoiseHg\hg.exe" push --cwd "c:\[BackupDirectory]"

So what happens now: when this batch-file is executed, it will make a backup of the DB, add the file (if its not already in the repository), commit the changes to the local repository and then push it to the server.

To automate this, I used the Windows Task Scheduler to execute this batch every day. That's it :)

Of course you should also have a backup of your mercurial server repositories for this to make sense at all: but this is another story.