Backup up a windows server using only free tools

This site is run as a hobby and as such is costing me a few dollars every month. So in order to keep expenses small, and because its fun to tinker,  I wanted to see if I could create a solid backup solution for this windows server with out it costing me anything at all (besides time).

 

First off I made a list of what I need to back up, for me this ended up being the following items:

  • MSSQL.
  • MySQL.
  • All website files.
  • SVN repositiories.

 
I then made a general outline of how I should go about doing my backup..

  1. Copy everything that needs to be backed up into a temporary directory.
  2. Zip the directory with as much compression as possible.
  3. FTP the zipped backup to an off site location with some sort of transfer encryption.
  4. Avoid differential backups like the plague, I don’t want any backup depending on another file that just happens to be corrupt..
    Bandwidth and space is cheap any ways.
  5. Schedule the process to run every night.
  6. Use a single .bat file for doing as much as possible.

The complete backup script ended up being a single .bat file with dependencies on 7zip and an ftps library, both of which are free for commercial use. Keep reading to find out all the details ;)

 
 

Folder structure

My goal is to fill the directory c:/Server/backup with everything that I want to save. For me that means I will have a structure that looks like this:

Folder Structure



 

Timestamp

I started off my Backup.bat file with creating a time stamp variable that I could use to tag things with the current date and time as needed.

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Generate time stamp for tagging files, Use %TODAY%
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

for /f "tokens=2-4 delims=/ " %%a in ('date /T') do set year=%%c
for /f "tokens=2-4 delims=/ " %%a in ('date /T') do set month=%%a
for /f "tokens=2-4 delims=/ " %%a in ('date /T') do set day=%%b
for /f "tokens=1 delims=: " %%h in ('time /T') do set hour=%%h
for /f "tokens=2 delims=: " %%m in ('time /T') do set minutes=%%m
for /f "tokens=3 delims=: " %%a in ('time /T') do set ampm=%%a

@For /F "tokens=1,2,3 delims=/ " %%A in ('Date /t') do @( 
  Set Day=%%A
  Set Month=%%B
  Set Year=%%C
  Set All=%%C%%B%%A
)

set TODAY=%All%_%hour%%minutes%

This script sets the variable ‘TODAY’ to “YYYY-MM-DD_HHMM” ie: “2013-01-01_0300″. Note that this is using the 24 hour format, and 3pm will be 1500.

 
 

MSSQL

I then set up MSSQL to create a full (not differential) backup of all databases to my backup folder: C:/Server/Backup/mssql at 1am every night. The reason for using full backups is that I want each backup file to be self contained and to not require any other files if disaster should strike. The MSSQL backup is scheduled to run 2 hours before the rest of my script as an MSSQL Job.

If you are unsure of how to do this follow the link below to the Microsoft Technet website for an easy to follow video tutorial: Technet: Create a Maintenance Plan to Backup all Databases

 
 

MySQL

For Mysql I’m using mysqldump.exe (comes with MySQL) to create individual backup files for all my MySQL databases. The manual for mysqldump can be found here. The code below will create an .sql file containing the database structure and data for each database you have in your mysql server in the backup folder. Remember to give your backup user access to all the databases and to change directory paths as needed in the script.

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: MySQL Backup
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

:: MySQl DB user
set dbuser=BackupUser

:: MySQl DB users password
set dbpass=BackUpUserPassWord

:: Switch to the MySQL data directory and collect the folder names
pushd "C:\Server\databases\mysql\data"

:: Loop through the folders and use the file names for the sql files, collects all databases automatically this way
:: Pass each name to mysqldump.exe and output an individual .sql file for each

FOR /D %%F IN (*) DO (
  "C:\Program Files\MySQL\bin\mysqldump.exe" --user=%dbuser% --password=%dbpass% --databases %%F > "C:\Server\backups\mysql\%%F.%TODAY%.sql"
)

 
 

Web sites

The next step was to backup all the web site files. I tried using a few different copy methods but in the end I decided for the ROBOCOPY command which does the job nicely.

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Copy www to backup dir 
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
set webbackuptarget=C:\Server\backups\www
set webbackupsource=C:\Server\www

:: clear out the old data
rd /S /Q %webbackuptarget%
mkdir %webbackuptarget%

:: copies any file from www directory that is 99 mb or smaller.
:: /MAX: command limits filesize so that only smaller files than given bytes are copied
:: I dont want to bloat the backup with big files and if a file is over 99 mb 
:: I need to keep it backed up in some other way.
ROBOCOPY /E /MAX:103809024 /R:5 /W:5 /A-:R /MIR %webbackupsource% %webbackuptarget% 

 
 

SVN

I also wanted to make sure that my SVN repositories are kept safe so I added them into the backup. I am using Visual SVN and their documentation describes how you should backup your repositories.

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Copy SVN to backup dir 
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

set svnbackuptarget=C:\Server\backups\svn\repository
set svnbackupsource=C:\Server\svn\repository

:: clear out the old data (delete and recreate the directory)
rd /S /Q %svnbackuptarget%
mkdir %svnbackuptarget%

"C:\Program Files\VisualSVN Server\bin\svnadmin" hotcopy --clean-logs %svnbackupsource% %svnbackuptarget%

 
 

Compress

Once everything that needs to be backed up is finally in the backup directory I used 7zip to compress it all into a .7z file. I originally wanted to use the built in zip functionality in windows but 7zip was so much easier to use that I went for that instead. You can download it from here, and it is free for commercial use.

I experimented a bit with the options and the settings below gave me the smallest file in my tests.
You can read more about 7zip here

I’m compressing all my files to a local /log folder which I empty out as needed every few months.

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Compress Backup directory
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

set DIRTOZIP=C:\Server\backups\*
set DESTINATION=C:\Server\scripts\backup\log\
set TARGETZIP=%DESTINATION%Backup_%TODAY%.7z

"C:\Program Files\7-Zip\7z.exe" a -mx9 -r -t7z %TARGETZIP% %DIRTOZIP%

 
 

FTPS

When 7zip is done compressing the files I want the backup to be copied to some place off site in case anything happens to the server that stops me from getting into it. For me this means copying the file to a NAS that I have running at home, but any FTP server that you can use will do the trick.

The difficulty here is that I want the transfer to be encrypted and Windows built in ftp command does not allow you to connect to FTPS servers so I was forced to look else where for this functionality. Fortunately I found Alex FTPS Client on Codeplex which does a great job of transferring my files. The program does not require an installation and is very simple to use.

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: FTP the backed up file
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

C:\Server\scripts\backup\ftp\ftps -h myOffSiteFTPServer.com -port 21 -U FTPBackupUser -P ReallyHardPassword -sslInvalidServerCertHandling Accept -p %TARGETZIP% /RemoteDirectory/Backup_%TODAY%.7z

 
 

Complete source

Below is the complete Backup.bat file. If you have any questions don’t hesitate to ask in the comments. Good luck and let’s hope we never have any use of these backups.. :)

Backup.bat, 1.29 kB, 86 downloads.
  • Chamanga

    Thanks, got rid of an expensive backup program on my server and did it this way.. works great!

  • widnowslower

    Thanks for the guide! Im using the Mysql and ftp parts

2012/12/30 Uncategorized Jens Berfenfeldt