Copying data from one table to another (differents servers)

Hi,
I'm Roby Eisenbraun Martins. I'm a C++,VB and C# developer.
I need to work with an offline database. This database will have a
table, just like the server, Where I need to copy a filtered group of data.
The ideia is to keep my local table synchronized with the filtered group
of data, so each time I have to delete all data from the table and copy it
all again.
Should I use snapshot?
Or can I copy the data from one server to another without linkedservers?
Thank you,
Roby Eisenbraun Martins
there are a multitude of ways to do this. Transactional replication,
snapshot, bcp, dts (SSIS in SQL 2005).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roby Eisenbraun Martins" <RobyEisenbraunMartins@.discussions.microsoft.com >
wrote in message news:F5C16F54-6F6C-413B-B991-3EC8353872CA@.microsoft.com...
> Hi,
> I'm Roby Eisenbraun Martins. I'm a C++,VB and C# developer.
> I need to work with an offline database. This database will have a
> table, just like the server, Where I need to copy a filtered group of
> data.
> The ideia is to keep my local table synchronized with the filtered
> group
> of data, so each time I have to delete all data from the table and copy it
> all again.
> Should I use snapshot?
> Or can I copy the data from one server to another without
> linkedservers?
> Thank you,
> Roby Eisenbraun Martins
|||and ....
"Hilary Cotter" wrote:

> there are a multitude of ways to do this. Transactional replication,
> snapshot, bcp, dts (SSIS in SQL 2005).
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Roby Eisenbraun Martins" <RobyEisenbraunMartins@.discussions.microsoft.com >
> wrote in message news:F5C16F54-6F6C-413B-B991-3EC8353872CA@.microsoft.com...
>
>
|||I would suggest using DTS/SSIS (depending upon your version) to do this.
While replication sounds tempting, there are a variety of situations which
happen in development environment which would create problems with the
replication engine. That introduces errors into your production environment
that you wouldn't otherwise have. You are much better off to simply leave
them decoupled and use DTS/SSIS to extact and load the data on demand.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Roby Eisenbraun Martins" <RobyEisenbraunMartins@.discussions.microsoft.com >
wrote in message news:F5C16F54-6F6C-413B-B991-3EC8353872CA@.microsoft.com...
> Hi,
> I'm Roby Eisenbraun Martins. I'm a C++,VB and C# developer.
> I need to work with an offline database. This database will have a
> table, just like the server, Where I need to copy a filtered group of
> data.
> The ideia is to keep my local table synchronized with the filtered
> group
> of data, so each time I have to delete all data from the table and copy it
> all again.
> Should I use snapshot?
> Or can I copy the data from one server to another without
> linkedservers?
> Thank you,
> Roby Eisenbraun Martins

Copy VS Create

Hello,

We are building a smart client that ships with it an offline db - Sql Express.

I wanted to check if there is a preferred/recommended approach to deploy the database ? I'm essentially asking between

1. Copying a database file and

2. Creating the database file by connecting to Master and issueing create database etc and then running the scripts as we need it.

Any help will be great,

Thanks,

Avinash

PS: The app is a multi user app and may be used by multiple windows users on the same machine. So we currently use the Auto Attach feature in the connection string with database files in the Special Application/User Data folder. So we isolate files b/w users.

Could you clarify your definition of "offline data" with your comment that this is a multi-user application? Do you expect multiple people to be using the same database simultaneously or should each user have a separate offline database?

Thanks for the additional informaiton.

Mike - SQL Express team

|||Hello Mike,

Its a multi user application in that - several windows users may use the application installled on one machine. Not at the same time. Currently we have one copy of the database per windows user logging into the machine/application in the User Data special folder.

So, yes its one database file per user but many such files may exist on a machine one per user. And only one user may use the machine/application at a time. However this may be slightly different in a WTS - terminal service environment. But even then the idea of one offline database file per user will not change.

Any advise will be appreciated and will help a lot..
Thanks,
Avinash
|||

Hi Avinash,

Thanks for the additional information. Since you are really looking for each user to have a unique database, I would embed the database into your application and then use User Instances to handle the attach/detach of the database. User Instances are new in SQL Express and are used by VS 2005 when you insert a database into your applciation. Check out the User Instance white paper for more information.

One of the main benefits of User Instances is that the person running the application doesn't have to be an admin user or have sa permissions to deal with things like attach and detach. Normally a user must have elevated permissions to do things such as this.

Once you've embedded a database in your application, you can also use ClickOnce deployment to get your application to your users. You should be able to find out more about ClickOnce in the VS documentation.

Regards,

Mike Wachal
SQL Express team

|||

Hello Mike Wachal,

One last question - if we generate the database file on a particular version of ms sql express and in time to come if there are a few more versions of ms sql express out there with the users - if we continue to package with our app a database file from an older version of sql express will that be a problem with users running a newer version of sql express ?

Also what about the converse scenario - where we ship a database file from a newer version of sql while the customer is still running on an older version.

Thanks,

Avinash

Copy sql server 2005 database on the same machine

How can I create a copy of an existing sql server 2005 (live or offline) database and put it on the same server in a test database ?

First, make a Backup.

Then, look in Books Online about using RESTORE ... WITH MOVE.

Example:

Code Snippet

BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'

RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'

|||Thanks for the reply. Your response was very helpful.