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 '09 at 07:20 AM in Default

user-407 (google) gravatar image

user-407 (google)
1 1 1 1

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

2 answers: sort oldest

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 '09 at 10:59 AM

    Kristen gravatar image

    Kristen ♦
    2.2k 6 7 10

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

    Seems you need to use Replication

    more ▼

    answered Nov 12 '09 at 09:32 AM

    Madhivanan gravatar image

    Madhivanan
    1.1k 1 3 6

    (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.

    New code box

    There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

    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:

    x1933
    x1816
    x293
    x186

    asked: Nov 10 '09 at 07:20 AM

    Seen: 1226 times

    Last Updated: Nov 10 '09 at 07:20 AM