Backing up data between SQL Express 2005 and SQL Server 2008 Standard Edition
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?
any help is much appreciated.
Best Answer
As an alternative to Replication you could create a Linked Server - this will enable one server to be able to "see" the other, and run queries on it.
So you could do, for example:
INSERT INTO MyTable SELECT * FROM MyRemoteServer.MyRemoteDatabase.dbo.MyRemoteTable
If you have a "Modified Date/Time" column on your tables you could use this approach to "Pull" / "Push" data that is newer between the servers.
Or you could have a column for BatchNumber and us that as a basis for transactions that had not yet been "posted" to the other server e.g.
- Get next available batch number
- Update all rows with no batch number with the "Next Number"
- Copy all rows with batch = "Next Number" to the remote server
Seems you need to use Replication
