I have started and stopped this post probably about 10 times now.  I just didn’t feel it was that interesting.  But there probably isn’t a better time to capitalize on this post than this week, because of a certain few widely known bloggers, who should have known better, and had their websites go down with out any backups. 

This post won’t be as flashy as Robs method, but it has worked very well for my WordPress, MySQL, and Windows deployment for 2 and 1/2 years.  So here is the original post.

This method of backing up your web server very cheaply has actually been deployed on this server since mid-2007, so it has been working flawlessly, with very little maintenance and work over the past 2 and 1/2 years.  And it has saved my but once or twice.

Server Setup

Before I jump in to the specifics, this post is geared towards a WIMP (Windows, IIS, MySQL, and PHP) install, or more specifically a Windows WordPress install. Here are the tools that I am using to accomplish this:

• 7-zip (7-zip.org)
• MySQL (mysql.com)
• Windows 2003 or better
• Scheduled Tasks
• Command Terminal
• GoDaddy domain (godaddy.com)
• Remote Desktop Access

Make sure you install everything from above that you haven’t already done so on your server.

GoDaddy Domain w/ Free FTP

The first thing you need to do is buy a from GoDaddy if you don’t already have one, and then venture over to setup your free hosting that came with your domain.  With this hosting you will get an FTP account to access this hosting account.  I am not going to cover this anymore, because if you need help GoDaddy has many resources that will help you get started. 

Get your FTP setup with your username and password for the FTP access and put those aside you will need them for later.

Backup Batch File

From this point on we are going to get in to the real meat of the backup process.  The first thing we are going to want to setup is the batch file that will run on the schedule task. 

The first part of the batch file is the setup for the processing.

@echo OFF
CLS
Title Website Backup

@rem Date Configuration
for /f "tokens=1-4 delims=/ " %%a in ('date/t') do (
set weekday=%%a
set month=%%b
set day=%%c
set year=%%d
)

@rem Backup Configuration
set servername=localhost
set database=managedfusion coderjournal
set backupdir="C:\websites"
set supportdir=%backupdir%\_support
set databasedir=%backupdir%\_db_backups
set ftpcommands=%supportdir%\ftp-commands.txt
set logfile=%supportdir%\backup.log
set zipfile=%backupdir%\backup.%weekday%.7z
set sqlfile=%databasedir%\backup.%weekday%.sql
set zip="C:\program files\7-Zip\7z.exe"
set mysqldump="C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe"
set mysqluser=root
set mysqlpassword=FlufflyBunnies1234

set start=%date% - %time%: Database Backup of %database% Started

A couple things that you are going to want to change right away are the database, backupdir, and mysqlpassword.  (By the way I was that is not my real password.)  Nothing really interesting here, I made this batch file very configurable, so that I could use it in future deployments.  Another interesting setting you will want to look at is the sqlfile which has a special %weekday% command in it that will insert the day of the week.  This means that if something goes wrong you will have the last week worth of data to look through.

The next thing we want to do is clean up any logs that where left around last time the process was run, and start the logging process for this run of the batch file.

@rem Remove Old Log
del /f /q %logfile%

@rem Start Logging
echo %start%
echo %start% >> %logfile%

The logging isn’t too detailed, just that the batch file started and stopped and how long it took.  Next we are going to use a special program, that comes with MySQL, called mysqldump.exe, which can be used to dump the schema and the data in the databases. 

@rem dump database. This is all one line
del %sqlfile%
%mysqldump% --user=%mysqluser% --password=%mysqlpassword% --comments --create-options --extended-insert --tz-utc --result-file=%sqlfile% --databases %database%
if not exist %sqlfile% goto FAIL_DUMP

This will dump the entire schema and data in to a SQL file that can be used to rebuild your entire database as of the time the backup was run if you ever needed to do so.  After the database is backed up, which could take some time depending on the size of your database, it is now time to create an easily transferable file.  Because you will have your entire database file, as well as your images, and anything else that is deployed on your website.  To do this I choose to use 7-Zip, which has a very nice command line tool, to compact everything in to one easily transferred file over FTP.

@rem Zip up database
del %zipfile%
%zip% a -t7z -p%mysqlpassword% -mx=1 -mhe=on -x!*.7z -r -y %zipfile% %backupdir%\*
if not exist %zipfile% goto FAIL_GZIP

I also used the MySQL password to secure the file, just as an extra added layer of protection.  Next we are going to FTP our newly created zip file to our hosting previous created from GoDaddy.  In Windows FTP can be done from the command line, but it takes an extra commands file, that feeds the FTP command line a list of commands to execute against the server.  The code I am going to show you is part of the batch file.

@rem FTP archives offsite
ftp -n -s:%ftpcommands%

Very simple right.  The next thing is the list of commands which sits in another file appropriately called ftp-commands.txt that sits right next to the batch file we have been creating.

open
myFTPhosting.com
user
myUSERaccount
FluffyBunnies1234
bin
put backup.Mon.7z
put backup.Tue.7z
put backup.Wed.7z
put backup.Thu.7z
put backup.Fri.7z
put backup.Sat.7z
put backup.Sun.7z
quit

You are going to want to change the following:

• Line 2: your domain name for the FTP hosting you setup above
• Line 3: your FTP username
• Line 4: your FTP password (still not my password I swear)

After you modify those lines save the file and we can continue with the batch file setup for the backup.  The rest really doesn’t need much explaining, because it is just logging and error handling.

@REM All is well
GOTO SUCCESS

:FAIL_DUMP
SET message=%date% - %time%: Database Dump of %database% Failed
GOTO END
:FAIL_GZIP
SET message=%date% - %time%: Backup Compression of %database% Failed
GOTO END
:SUCCESS
SET message=%date% - %time%: Backup of %database% Completed Succesfully
GOTO END

:END
ECHO %message%
ECHO %message% >> %logfile%

Scheduling Your Backup

The next thing we need to do is setup a schedule for how often we want to run this batch file we have just created.  Just open up the Task Scheduler and click Create Basic Task… to bring up the wizard window.

Just continue through the wizard and setup the settings how you want. I choose it to run daily at 1:00 AM.

Make sure to fill in the Start in field to be the root of your website, even though it is not the location of your batch file.  This is because the batch file is setup from the perspective of that it will be running in the root of the website.

After you have finished this wizard kick it off once just to make sure everything is running fine.

Sit Back And Relax

You now have a daily backup of your website.  So sit back and relax.  If your server ever fails, you need to switch hosts, or reproduce the website on your local machine for some development.  You have an easy backup of everything you need to get rolling in a relatively small amount of time.  Plus as an added bonus you don’t have to lose sleep over not having any backups of your website that you have spent countless hours creating.

Like I said this process has been running with out problems since I started this blog 2 and 1/2 years ago. So you can rest assured that it will work for you, if you follow the steps above exactly as they are laid out.

Tags: backup

Posted in How To | | | View blog reactions | No Comments »

I recorded the following presentation for DevReady.NET, a new project that I am working on with a bunch of talented MVC’s and Microsoft employees.

After I get done with editing the presentation, I will post it up to DevReady.NET and my blog.  This will be my first try at making a video presentation for the web, so I look forward to your comments. 

Tags: Presentation

Posted in ASP.NET, C#, Portfolio | | | View blog reactions | 2 Comments »

Looks like some great new improvements coming to the 1.4 release.  It doesn’t look like any new methods are being added, but updating many of the new ones released with 1.3.

There are a few areas in jQuery that have seen extensive changes since 1.3.2 was released:

• live was drastically overhauled and now supports submit, change, mouseenter, mouseleave, focus, and blur events in all browsers. Also now supports context and data.
• append, prepend, etc. have been heavily optimized.
• add has been adjusted to always return elements in document order.
• find, empty, remove, addClass, removeClass, hasClass, attr, and css have been heavily optimized.

Full details concerning the release are forthcoming – for now we just need your help in catch regressions. Some more details can be found in John Resig’s keynote at the 2009 jQuery Conference.

Grab the code:

• Regular: http://code.jquery.com/jquery-1.4a1.js
• Minified: http://code.jquery.com/jquery-1.4a1.min.js

NOTE: If you’re using jQuery 1.4a1 and you run into an error please make sure that you’re using the regular version of the code, it’ll make it easier to spot where the error is occurring.

Tags: jQuery

Posted in News | | | View blog reactions | 1 Comment »

The title of this post is a tongue-in-cheek reference to SubSonic’s inline query by the same name, which in turn is a reference to the blogger Jeff Atwood’s blog who you should all know.  Rob Conery, the SubSonic project leader, named the inline query class CodingHorror after he allegedly read Jeff Atwood’s post titled Embracing Languages Inside Languages, in which he bestowed the virtues of inline SQL inside your code, instead of the standard bequeathed statement that I bet you all have heard “We do all database work in stored procedures.”.  Jeff outlined his though process on ad-hoc vs stored procs as follows:

The Subsonic project attempts to do something similar for SQL. Consider this SQL query:

SELECT * from Customers WHERE Country = "USA"
ORDER BY CompanyName

Here’s how we would express that same SQL query in SubSonic’s fluent interface:

CustomerCollection c = new CustomerCollection();
c.Where(Customer.Columns.Country, "USA");
c.OrderByAsc(Customer.Columns.CompanyName);
c.Load();

I’ve mentioned before that I’m no fan of object-oriented rendering when a simple string will suffice. That’s exactly the reaction I had here; why in the world would I want to use four lines of code instead of one? This seems like a particularly egregious example. The SQL is harder to write and more difficult to understand when it’s wrapped in all that proprietary SubSonic object noise. Furthermore, if you don’t learn the underlying SQL– and how databases work– you’re in serious trouble as a software developer.

Enough of the History lesson, why are you writing this post

Well the reason for this post is two fold, I haven’t written a post in a couple of weeks, and I wanted to write about my own CodingHorror moment, while working with the Entity Framework, that solved a big problem I was having with getting the LIKE statement to work in LINQ.

I am working with a client of mine trying to expose their data to the web via a simple REST service.  Their whole database model has been constructed in the Entity Framework in a simple active record format.  One of the requirements, I was given, was to support wildcards on a couple of the input fields.  I thought this was no big deal, because I knew that the StartWith, EndsWith, and Contains methods of the System.String object, when used in LINQ, translated down to the SQL LIKE operator. 

However at the time I didn’t anticipate how much of a pain in the butt it was to actually figure out which of these three methods I should use depending on where the wildcard was placed in the string.  And how I would support a wildcard that was placed in the middle of a string.

Enter Entity Frameworks’s parameterized Where method

The parameterized Where method, of the ObjectQuery<T> object, really saved my butt.  It allowed me to produce the exact effect of the query I was looking for, and with out a lot of hacking.  Here is what I did:

if (!String.IsNullOrEmpty(toAddress))
	table = table.Where(
		"it.ToAddress LIKE @toAddress",
		new ObjectParameter("toAddress", toAddress)
	);

var results =
	from result in table
	where result.Account.AccountId == accountId
		&& result.DateTime >= startDate

return View(results.ToList());

The best part of the above code is that I can still use LINQ, and jump in to standard SQL syntax when the LINQ syntax fell short. 

This is my opinion gives the Entity Framework a step above the rest, because it means that I don’t have to complicate my life and my program by moving to a stored proc or making the whole select statement inline-SQL (which I wouldn’t ever do). 

Features like this that make developing software easier, and obviously have the programmer in mind, are what we should all strive for.  I like this for the very fact that the Entity Framework team has acknowledged that LINQ, while a wonderful addition to .NET, doesn’t meet all the needs for pulling data from the database and they sought to minimize the short comings by allowing developers to jump back in to the natural SQL language.  This is a wonderful addition in my book.

Tags: Codinghorror, Entity Framework, SubSonic

Posted in C#, How To, Programming, SQL | | | View blog reactions | 2 Comments »