schema extraction for multiple interdependent databases

[RugbyCoach]

I'm looking for a tool that can extract only the schema from a database
in a form that can be used to generate that schema in another empty
database. This is to facilitate our disaster recovery processes where
we need the objects only, not the data, and need to replicate this to
our disaster recovery site over the WAN. There are plenty of tools
that can handle a single database, but does anyone know of any tools
that could handle multiple databases where many of the objects (stored
procs and views) are dependent on objects in other of the databases
(tables). This is a home-grown ETL suite so making changes to the code
to remove these dependencies would take way too much effort. I am
looking for something that can either extract the schema for all 3
databases and handle the object creation ordering to account for the
dependencies (a simple method would be to extract by object type across
all databases, e.g. tables for all dbs before views before procs), or a
backup/restore tool that allows you to restore the objects only without
data. Worst case we could write something to generate the DDL or use
SQL DMO, but ideally we would prefer to purchase a (relatively
inexpensive) tool to do it.

Thanks,
Simon

[Simon Hayes]

"RugbyCoach" <si***********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I'm looking for a tool that can extract only the schema from a database
in a form that can be used to generate that schema in another empty
database. This is to facilitate our disaster recovery processes where
we need the objects only, not the data, and need to replicate this to
our disaster recovery site over the WAN. There are plenty of tools
that can handle a single database, but does anyone know of any tools
that could handle multiple databases where many of the objects (stored
procs and views) are dependent on objects in other of the databases
(tables). This is a home-grown ETL suite so making changes to the code
to remove these dependencies would take way too much effort. I am
looking for something that can either extract the schema for all 3
databases and handle the object creation ordering to account for the
dependencies (a simple method would be to extract by object type across
all databases, e.g. tables for all dbs before views before procs), or a
backup/restore tool that allows you to restore the objects only without
data. Worst case we could write something to generate the DDL or use
SQL DMO, but ideally we would prefer to purchase a (relatively
inexpensive) tool to do it.

Thanks,
Simon

It sounds as if the scripting functionality in EM could do what you want,
but is your aim to automate the scripting process? If so, then something
like this might be what you want:

http://www.red-gate.com/sql_comparis...on_toolkit.htm

Just out of curiosity, in a disaster recovery situation, why would you want
the objects but not the data? Why not use replication or log shipping to
maintain a standby copy of your databases?

http://support.microsoft.com/default...b;en-us;822400

Simon

[RugbyCoach]

Thanks for the reply. I took a brief look at Red-Gate but I didn't
know if it could handle the cross-database dependencies that are
prevalent in this suite of databases. Do you know if it can do this?

On the DR side, the reason why we don't need the data is because it is
an ETL suite. In our design one of the databases is purely used as a
staging area for input files and one is used purely for staging output
files. Therefore, these databases do not contain any history - so we
don't need the data at our DR site, just the objects so that our code
will work. We currently log ship these databases because this gets us
around the cross-database dependencies that would bite us if we copied
schemas one database at a time. However, as you would expect, the
transaction volume is pretty high and is consuming too much of our WAN
bandwidth during peak processing hours.

Thanks
Simon

[Gary]

What do you mean by cross-database dependencies? I don't believe SQL
Server checks or maintains cross-database dependencies until run
(execute) time.

I have automated SQL DMO scripts which script out multiple databases.

The key to successfully restoring would be to add the objects in the
correct order. Tables, then constraints (PK, FKs, etc), then stored
procedures. Loading stored procedures twice ensures that procs calling
other procs correctly finds the dependencies.