Greetings,

I'm trying to determine what the best way to go about the following
would be:

1. After making modifications of structure and data to a production
database, I want to make a backup of that db and copy it to multiple
standalone machines to import for further use as part of a sample
demonstration system.
2. But, there are several from which tables I do not want to take the
data from production, especially ones with large binary objects.
3. Instead, I want to store a set of "standard" replacement data
scripts for those tables which is always used in the sample
demonstration systems as a standard configuration.

So, to recap, the task would be:

1. Finalize source database.
2. Transform the source database (by way of DTS?) such that all tables
not related to demonstration draw their data from the source, but all
tables whose data should come from the standard configuration have
their data drawn from the scripts (or some other source)
3. Finalize the target db as a .bak file so it can be placed on to
standalone machines.


I have seen some other possibilities for step 3 as well, like making
an EXE that automatically can update a db. That is nice too.
Ultimately, I would like for users who will operate the standalone
demonstration systems to be able to click a button somewhere within
the start menu and have the demonstration database be updated with the
target db created in step 3 above. Currently, users bring the machines
to support staff and support staff manually open Enterprise Manager
and execute a stored .sql script that performs a "restore with
replace" to take the .bak file's contents and overwrite the installed
system's data with it to revert the configuration.

All tips much appreciated,
Thank you
JSG

Personally I would do it like this

1. Back up the production DB
2. Restore to n standalone machines as is.
3. I would a script file that does the emptying and reloading of those
tables for which you want to exchange the data.


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"JSG" <jsgough (AT) mindspring (DOT) com> wrote

Thanks Allan,
That is similar to what we currently do. The biggest problem with it
now is that one of the tables contains almost 200 megabytes worth of
attachments that are not needed and thousands of other records that
are not needed.

I am currently doing the backup using a straight backup to .bak file.
But, I think I can accomplish it using a query to specify which data I
want. I will look into that more.
Thank you,
JSG
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote