Moving Data tables between SQL Servers

[Tom Loach]

I have user that we just migrated his Access database to SQL
Server. All went well with the migration, but then he came up with
another requirement to be able to replicate the database to a local
SQL server living on the hard drive of a laptop. Before the migration
he just copied the entire Access databse to the lap top.
I tried using the Copy SQL Server Objects Task to move the
necessary tables from the production server to the laptop, but noticed
it doesn't copy over the table Indexes/keys identiy fields etc. I
ended up backing up the production database and restoring it to the
laptop database, but wondered if there is any way to move the tables,
with their properties from one server to another? I know I can set
up the backup process to run as scheduled, but the problem is the data
needs to be moved on an irregular time table. I thought about just
writing code on the remaining Access front end to empty the local
tables and then query the data from the production side to reload
them, but I'm sure there's an easier way.
Any suggestions would be appreciated.
Thanks,
Tom

[Erland Sommarskog]

Tom Loach (tc***@virginia.edu) writes:

I have user that we just migrated his Access database to SQL
Server. All went well with the migration, but then he came up with
another requirement to be able to replicate the database to a local
SQL server living on the hard drive of a laptop. Before the migration
he just copied the entire Access databse to the lap top.
I tried using the Copy SQL Server Objects Task to move the
necessary tables from the production server to the laptop, but noticed
it doesn't copy over the table Indexes/keys identiy fields etc. I
ended up backing up the production database and restoring it to the
laptop database, but wondered if there is any way to move the tables,
with their properties from one server to another? I know I can set
up the backup process to run as scheduled, but the problem is the data
needs to be moved on an irregular time table. I thought about just
writing code on the remaining Access front end to empty the local
tables and then query the data from the production side to reload
them, but I'm sure there's an easier way.

I don't know the exact requirements of this user, but backup/restore
sounds the safest and soundest way to go. A simple way would be to
backup fairly often, and when the user connects he can restore as
he wishes.

Another alternative is to set up replication. This can work more seamlessly
once you have it running, but it takes more work to go there.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp