I want to store a copy of my gate.com-hosted SQL Server 2005 database
table structure and data onto my home computer, essentially to serve
as a backup. Preferably, I'd like to create a file that contains SQL
statements which could be used to recreate my database.

I'm fairly inexperienced with database work, and maybe that's why I
thought that it might be a simple matter of executing an appropriate
SQL query to get the resulting file into a directory on my gate.com
account, then simply ftp that file onto my home computer. However,
I've had no luck in executing any such SQL query to create a
backup/dump file.

I suspect that the non-free version of Microsoft's SQL Server
Management Studio might be a good tool for this, but I'd rather not
have to buy it, plus I'd prefer to take a simple route (if it exists)
rather than have to buy and install that software.

Searching the web has made me believe that creating a file with the
SQL statements to recreate my database could be done with a statement
similar to:
BACKUP DATABASE '222222_default' TO DISK = '~/database/DUMP.BAK'

However, this and other variations of queries that I've tried to
execute (with the help of Visual Studio) always seem to fail.

Management studio is the best way to go.  You essentially generate
scripts for the database, run them locally, and then export the data
(with identity insert set ON).  It is generally best to do it in two
steps.

If this isn't an option (due to cost) I'd suggest:

1. Ask your database host to create a backup of the file and make this
available to you.  You can then restore locally.

2. Get an evaluation copy of the fantastic dbghost
(http://www.dbghost.com/home.aspx) and use this.

OK, thanks very much for the feedback!

You need to add a dump device first.  Try this:
EXEC sp_addumpdevice 'disk','222222_DUMP','c:\database\222222_DUMP.BAK'
BACKUP DATABASE 222222_default TO 222222_DUMP