I have a database in a server in Africa, and need a daily sync\restore to a server in Europe.

My first idea was to sync the data between the 2 databases, but the database has more than 1000 tables, and is not well built, so the software I've tested didn't work great.

For what I've read the best option is to backup the database in one server and restore it to the remote server.

Is this the best solution?

Is Redgate Backup Pro the best tool for the task?

I've been playing with it and it seems to have everything I need: backup\restore scheduling, compression, etc.

Need some advice on this. Thanks in advance :)


3 Answers 3


Scheduling backups and restores via the SQL Agent isn't very hard. It is only one or two lines of TSQL. Just make sure that the backup finishes before the restore starts.

You do not mention which version or edition of SQL Server you are using. Some versions/editions of SQL Server support Backup compression natively. (Here is the page that talks about the capabilities of the different editions of SQL Server 2012.) If you are not using a version/edition of SQL Server that supports compression, RedGate's backup compression should be a reasonable choice. (I've not used that particular compressor. I have used a competitor, SQL Lightspeed. I believe that the products are fundamentally the same.)

In the past, I've found the larger problem is getting the backup files from one location to the other, as it is often assumed that both servers are on the same LAN and domain and that you can simply use copy.exe or robocopy.exe. I was forced to cobble together my own FTP transfer schemes back then, which is more work and testing.

Another problem is getting the user IDs synced up with the login IDs after restoring the database to the target server. If you have already done this a few times, you know what I mean. If not, here is a link to a relevant SE thread.

  • In Europe we have SQL Server 2008 (not R2). I don't know if the client will use this edition in Africa or will use an Express Edition..
    – CSeven
    Jan 24, 2013 at 12:42

Have you thought about Log shipping? It might be worthwhile to consider it as another option. It can be faster than restoring whole database (of course it depends how many transactions you usually have per day, it can differ case by case). To do that you need follow the steps below:

Back up the transaction log at the primary server instance.

Copy the transaction log file to the secondary server instance.

Restore the log backup on the secondary server instance.


  • Log shipping is not working if the servers are not in the same domain. And I'd think they are separate domains, at least by thinking of the locations :-).
    – Marian
    Jan 24, 2013 at 23:28
  • 1
    With a few hours of coding, you can write your own log shipping with TSQL. You just follow the Backup->Copy->Restore workflow. You can copy via ftp (if you don't have a vpn, encrypt the file first, ftp is not secure) or you could use scp or something along those lines. Set up one Agent job to copy files to a local folder. Set up another job to check for and restore tlog backups. Once you have the file local to you, check to see if the tlog backup has already been restored. If not, restore it and then flag the file as restored. Repeat at intervals. You still must sync the logins and users. Jan 25, 2013 at 14:14
  • Well said Darin! That's it :)
    – Sky
    Jan 25, 2013 at 21:49

If you are able to create a Site to Site VPN between servers in Africa and Europe, you should be able to setup Log Shipping pretty easily. With Site to site you can just copy/paste the files i.e. eliminating FTP completely. With log shipping, you can even keep the database in Read Only/Stand By mode to query from if needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.