By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

schema extraction for multiple interdependent databases

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"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
Jul 23 '05 #2

P: n/a
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

Jul 23 '05 #3

P: n/a
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.

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.