Keeping an up to date copy of a SQL Server database on another server We outsourced some of our business functions to another company. That company makes use of the same software as we do and we would like to keep a update to date copy of their database on our servers for monitoring purposes.

Just to clarify what I mean by having an an update to date copy we don't mind if the database is only updated or synchronized once a day.

Last thing to keep in mind is that the connection between the companies is a 10 Mbps connection.

We are both running Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64).

How would you tackle this?

Edit

I don't expect the database to grow to more than 8GB in the next 2-3 years.
Just to clarify what I mean by having an an update to date copy we don't mind if the database is only updated or synchronized once a day.


Depending on the database size, I would use backup with compression and restore. You can easily automate this process.

Just take full backup with compression once every night, upload it to FTP (probably secure) and have a process on your end to download it and restore the database (overwriting the previous copy).

There are other methods like using SSIS - if you want selected tables only or using BCP OUT and BCP IN - provided you have same schema on your side.

Dont get into the hassle of implementing replication as making schema changes is a big headache + you have to generate snapshot and depending on the size of your database and network, it will have a performance impact on the source server.