question

user-407 (google) avatar image
user-407 (google) asked

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.

sql-server-2008sql-server-2005replicationbackup
10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

· Write an Answer
Kristen avatar image
Kristen answered

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
10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Madhivanan avatar image
Madhivanan answered

Seems you need to use Replication

10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.