Search code examples
sql-serversql-server-2005replication

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

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!

Solution

  • 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.