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

I'm in a situation where I need to create a partial copy of a database from a remote SQL Server (2005/2008 Express Edition) and store it in a local SQL Server (2005/2008 Express Edition) database. The local copy will have the same schema definition, but contain only parts of the data from the remote database.

The local database has to be given a unique name with parameters from C# code, similar to

TodaysDate_SerialNumber_MachineNumber_DatabaseName    
e.g.  20100622_1_3_DatabaseName

(Don't get caught up with the naming scheme, it hasn't been decided yet.)

I'm working in C# on .net 3.5 using VS2010.

My thoughts so far is to execute the create script for the database on the local SQL server from C# code, and then copy data from the remote database to the local database, filtered on what I actually need. The operations have to be started from C# code, but doesn't necessarily need to be all C# code. But I haven't decided yet. What do you think would be the best option for doing what I w开发者_JAVA技巧ant to do?

(Btw, if I'm being unclear, just let me know and I will try to update the question with more info.)

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.

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

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.