Create cheap backup for your home server

 

For years I’ve been running home server (actually since 2002) without any backup at all. I guess I was lucky, since in that time, only one machine failed, but it was only a motherboard. So all the data was still on my disks. But machine is getting old (actually current server is almost 10 years old :)) and I’m running more and more sites on it, it makes me more paranoid about hard disk failure.

With this in mind I started to search for a cheap (or free) backup system, which would save my SQL databases and all important files on server (eg. web sites, personal data, etc.).

There are a lot of solutions out there, but I went for a very simple one, and you need only a separate hard disk (actually, I have used 4GB USB stick, which was lying around my desk). And a few scripts.

So for backing up SQL databases I’ve found (thanks to Mladen Prajdič) a cool script, which does exactly that – backs up all the databases. With one handy feature, it names .BAK files with date and time of created backup and can even delete old backups, so if I forgot to delete old backups, 4GB of backup drive is still enough (for my purpose). You can get the script here. There are several steps to follow, so that this will work:

• First open SSMS (SQL Server Management Studio) and connect to your SQL Server instance
• Run the script on “master” database – it will create new Stored Procedure under that database
• Go to SQL Server Agent and create “New Job” and name it eg. “backup_job”
• In left menu go to Steps and create a new one – type should be “T-SQL” and database should be “master”
• Paste this code:

  EXEC isp_Backup     
  @path = 'Z:\sql_backup\', 
  @dbType = 'User', 
  @bkpType = 'Full', 
  @retention = 22, 
  @bkpSwType = 'NV',
  @archiveBit = 1,
  @copyOnly = 0

• where I have setup backup path to Z:\sql_backup\, backup only User databases (I don’t need System ones), backup type is Full and delete backups older than 22 days. For more details about parameters, check the example code in script.
• After that you have to setup Schedules, so create new one and setup when do you want to create backup. I have setup every Sunday at 4AM, so that means I will keep last 3 backups (since I’ve setup to keep backups older than 22 days – you do the math :))
• That’s it for SQL part! Save everything, and test run if everything works just fine.

Hint: If you found yourself with error, when running the script, you should enable xp_cmdshell, which you can do with a tool Surface Area Configuration (in Start>Microsoft SQL Server 2005>Configuration Tools).

So, we’ve backed up databases now let’s do a simple backup (or copy) of physical files. I needed to backup only everything under C:\Inetpub\ (so all web sites and ftp files for IIS). I did use a command tool, called xcopy, since the normal copy doesn’t have some features, I need in this case. This tool is in Windows since NT4, so it should be there. Let’s just take a look at the code:

xcopy C:\Inetpub Z:\web_backup > Z:\logs\log%date:ned =%.txt 2>&1 /D /E /C /Y /I

So the first thing here is to check the switches:

• /D … Copies only files, that have changed after previous backup
• /E … Copies directories and subdirectories, including empty ones
• /C … Continues copying even if errors occur.
• /Y … Suppresses prompting to confirm you want to overwrite an existing destination file.
• /I … If destination does not exist and copying more than one file, assumes that destination must be a directory.
• This “>” and “2>&1” things means, that every (1 is normal and 2 is error) output of a program saves in text file.
• log%date:ned =%.txt With this I have some simple logs what was going on and when. It creates a file with a name logDD.MM.YYYY.txt.

But you have to be careful here, since %date% formats date as in your local settings, so if you live in area, where dates are written with backslashes “\” (which is a forbidden character for file names) you shoud also handle that.

There is only one more thing to do – save this line of code as “backup_files.bat” somewhere on disk and create a Scheduled Task (under Control Panel>Scheduled Tasks), that will run this BAT script every Sunday at 5AM for example.

So that’s it, you’ve done! For this backup system I’ve spend exactly nothing. I’m pretty sure, you also have some USB sticks lying around, which you don’t use at all.

In the next project I’ll try to create automated backup system to store backups in a cloud. Again, free version :) Do you know any solution for that? If you do, please leave a comment!

author: Aleš Rosina | Comments: 0 | Tags: backup-sql-server-bat-cmd-ssms-xcopy