Copy SQL Server data from one server to another on a schedule

This is a primary purpose of SSIS/DTS. I do exactly this with SSIS quite often. If you've not used SSIS before then sqlis.com is a great place to start (unless you're on SQL2000 then you'll use DTS, in that case start with sqldts.com). There are many great books on the subject as well.

But it is definitely worth your time to look into learning SSIS. Reading your question, SSIS is IMO the best route by far. You won't need to write a line of code, it has all the tools built in to do the job. You can also schedule it to run on an interval as well.

I'd write a .NET console app for this. Doing a backup is as easy as opening a SQL connection and executing a "BACKUP DATABASE [] TO DISK = N''. Then use the built in .NET FTP libraries to push it up to the other server. You can even throw in sharpziplib to zip it first. Then throw this into a scheduled task.

On the other side, set up a Windows Service that watches the folder that the ftp transfers into with a FileSystemWatcher and then restores the backup when done.

I found an open source tool that some people might find useful and could modify to suit their needs.

SQL Server Database Copy Tool
"Copies a sql server database from one server to another by backing it up, copying the backup file to the destination server and restoring the database."

HTH someone searching on this topic.