Backing up data between SQL Express 2005 and SQL Server 2008 Standard Edition

Question

Hi,

I am developing an application which needs to backup data between SQL Express 2005 and SQL Server 2008. My client runs with an installation of SQL express 2005 and needs to periodically back up data to a server database running on SQL Server 2008. The client db also receives some new data from server and needs to update itself. The question is HOW DO I DO IT ? plz help

Answer 1

There are many solutions depending on what you mean by periodically and the use of the SQL Server 2008 and SQL Server 2005 databases. Assuming that the SQL Express 2005 client database is an application that controls its own data but eventually needs to merge those changes into the SQL Server 2008 database, then Merge Replication probably would be a good solution. The SQL Server 2008 database would be the Publisher and the SQL Express 2005 would be the subscriber (SQL Express can only be a subscriber).

Answer 2

You can have a look at Service Broker. It can easily exchange data between SQL Server 2005 (even Express) and SQL Server 2008 as the protocol is completely compatible between 2005 and 2008.

Setting up SSB will be a bit harder than setting up Replication, is not just a wizard, you will have to write code to handle the transmission and receiving of your data. But is significantly more flexible, easier to upgrade later, higher performance and more scalable than replication based solutions.