Restore only few tables of the database

HI
I'm designing a database. This database will be installed in a few servers.
Some of the tables in the database the user can't change only once a month
the "center" should send their data to all clients. No connectivity between
installations (Replication isn't relevant). What we thougth to do is one of
the 2:
1. To have 2 databases, one will have all the tables and the other only the
"from center" tables, and once a month the center will send a backup of it
to be restore all over. the poblem is that we won't be able to perform data
integrity with Foreign key this way.
2. The other idea was to backup/restore only part of the database, maybe
depents on a schema or filegroup or so.
Is their a way to perform the 2nd way'?
Thanks"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> HI
> I'm designing a database. This database will be installed in a few
> servers.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity
> between
> installations (Replication isn't relevant). What we thougth to do is one
> of
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only
> the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform
> data
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
Can't you write something into your software to get this via the web and
update the tables? I don't think either of the options you've suggested are
the best way to go and you should not need to have 2 databases on the
client's machines.
> Thanks
>|||> 1. To have 2 databases, one will have all the tables and the other only the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform data
> integrity with Foreign key this way.
This is an option. And the downside you already know. You can consider using triggers to maintain
referential integrity, but I'd opt for option 3 below instead.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
This *might* be doable assuming SQL Server 2005. In 2005, you would have to separate these tables to
its own filegroup and set that filegroup to read only. You can now ship a filegroup backup and have
them restore that filegroup backup. However, I doubt it will work for you as you probably do
structural changes for these tables and structural changes affects the system tables which are in
the primary filegroup. In the end, the problem is that SQL Server won't let you restore a database
so different parts of the database are from different points in time. This would be an advanced
usage of the backup/restore features of SQL Server 2005, so you need to make sure that you are very
familiar with backup/restore. I would *not* even consider this route.
3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your clients execute using OSQL,
SQLCMD or through your own app. This is the, IMO, most robust solution and will give you the least
problems in the end.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chedva" <chedvag@.matrix-it.co.il> wrote in message news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> HI
> I'm designing a database. This database will be installed in a few servers.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity between
> installations (Replication isn't relevant). What we thougth to do is one of
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform data
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
> Thanks
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:emjgcQdHHHA.3468@.TK2MSFTNGP04.phx.gbl...
> 3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your
> clients execute using OSQL, SQLCMD or through your own app. This is the,
> IMO, most robust solution and will give you the least problems in the end.
Don't ALL apps of this nature (distributed to X no of clients) do this
anyway? Surely the time comes to update the structure of some tables and the
client surely doesn't do it themselves. We store a database version no and
run upgrades based on this version no. We also drop all stored procs, views,
indexes, functions, primary keys etc and recreate them. This last step is
probably not suitable for very large databases but works very well for us. I
find we very rarely do a release without some db change.
Michael