ansaurus

Question

How to do a partial copy of a remote SQL Server Express database to a local SQL Server Express database?

Answer 1

+1 A:

SqlBulkCopy is probably your best bet if you're doing this entirely in code.

You'll have sql connections to each of your databases, then a select statement to run on your source database which will insert rows into a destination database/table.

PaulG 2010-06-22 12:16:55

Without having investigated thoroughly, I like your suggestion the most out of the 3 I have seen so far. I'll do some research and some experiments tomorrow, and then I'll mark the answer that was most useful to me.

Sub-Star 2010-06-22 12:51:45

After being sidetracked, I now got around to this. SqlBulkCopy worked well for me. I query the database for all the tables in it, then for each table I query for the columns, and then I generate a Select statement that selects all columns from the table. I then create a SqlDataReader that I give to the SqlBulkCopy object, and voila! I use it to make an exact copy of the data in the database to another database with the same tables and schemas. I use the SqlBulkCopyOptions.KeepIdentity to make sure that my FK relations stay valid. So thank you @PaulG for suggesting the SqlBulkCopy class. =)

Sub-Star 2010-09-02 11:19:14

Answer 2

+1 A:

The answer to this really depends on what you are most comfortable doing.

I'd probably set up a linked server on the local SQL Express and write a single SQL Script to do the whole thing. It could report progress back to the C# app using RAISERROR ... WITH NOWAIT and these messages can be processed asynchronously by setting up a SqlInfoMessageEventHandler

Martin Smith 2010-06-22 12:23:13

I'm most comfortable using C#, although I should be able to create a SQL-script as you suggest. I'll do some tests to see what performs best. I have no experience with linked servers though, so I have to look into that. Do you know any good articles that explains linked servers? And an important question: Does linked server work between two SQL Server Express instances?

Sub-Star 2010-06-22 12:56:30

@Sub-Star Yes linked servers work on SQL Express. I can see pros and cons of both a C# and a SQL solution. The C# one might be somewhat less efficient as your application is orchestrating each individual step but things like error handling might be easier in C#.

Martin Smith 2010-06-22 13:09:45

Answer 3

+1 A:

Maybe you can create a backup of the database, download it to the local machiene, create there a new, empty database and restore the backup to the empty database with option "override". After that you can delete those records you don't want to "copy".

You could use Microsoft SQL Management Studio to create the backup- and restore-scripts.

Tobi 2010-06-22 12:28:57

ansaurus

tags:

views:

answers:

How to do a partial copy of a remote SQL Server Express database to a local SQL Server Express database?

related questions