x

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.

more ▼

asked Nov 10, 2009 at 07:20 AM in Default

avatar image

user-407 (google)
1 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Nov 12, 2009 at 10:59 AM

avatar image

Kristen ♦
2.2k 7 11 14

(comments are locked)
10|1200 characters needed characters left

Seems you need to use Replication

more ▼

answered Nov 12, 2009 at 09:32 AM

avatar image

Madhivanan
1.1k 2 5 9

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2091
x2017
x364
x237

asked: Nov 10, 2009 at 07:20 AM

Seen: 1582 times

Last Updated: Nov 10, 2009 at 07:20 AM

Copyright 2016 Redgate Software. Privacy Policy