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

Question

I have a pair of SQL Servers at different webhosts, and I'm looking for a way to periodically update the one server using the other. Here's what I'm looking for:

1. As automated as possible - ideally, without any involvement on my part once it's set up.
2. Pushes a number of databases, in their entirely (including any schema changes) from one server to the other
3. Freely allows changes on the source server without breaking my process. For this reason, I don't want to use replication, as I'd have to break it every time there's an update on the source, and then recreate the publication and subscription
4. One database is about 4GB in size and contains binary data. I'm not sure if there's a way to export this to a script, but it would be a mammoth file if I did.

Originally, I was thinking of writing something that takes a scheduled full backup of each database, FTPs the backups from one server to the other once they're done, and then the new server picks it up and restores it. The only downside I can see to this is that there's no way to know that the backups are done before starting to transfer them - can these backups be done synchronously? Also, the server being refreshes is our test server, so if there's some downtime involved in moving the data, that's fine.

Does anybody out there have a better idea, or is what I'm currently considering the best non-replication way to go? Thanks for your help, everybody.

UPDATE: I ended up designing a custom solution to get this done using BAT files, 7Zip,command line FTP, and OSQL, so it runs in a completely automatic way and aggregates the data from a dozen servers across the country. I've detailed the steps in a blog entry.

Thanks for all your input!

Answer 1

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.

Answer 2

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.

Answer 3

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.