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 🙂
Best Answer
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.