Hi!
I need to recreate the system tables (sysobjects, syscolumns,
sysindexes, sysforeignkeys, sysconstraints, sysreferences, sysindexkeys
at least) in another SQL server.
You may say "Thats easy! Backup and restore the database!" and I would
answer "I can't, the database size is above 50GB and I just can't do it
every time I need to recreate the info".
So, =BFDo you know any simple way to do these?

I don't need the data on the user tables and I'll log-in always as dbo
(using trusted connection), perhaps that helps.
Perhaps the simplest way is to do a bcp to backup and restore the data
but.... you never know what would happen. This is why I write ask you
guy, probably you know much more than me.

Thanks in advance

Another thing: I need to recreate them because the important thing is
the relation name-id on each table.
I have an application that has the Id of the objects in the sysobjects
table and I need to know it's name.

It's long to tell why I need the ids but I can tell you that is a third
party tool and it works that way, I can't change it. I also can tell
tou that I hate it because the way it works but I can't do anything
about it.
What you suggest is nearly imposible, I can't recreate the ids for
every primary key and every index.
i don't care if the database is messy, I just need the ids.
Is there any way to backup just the schema withou the data?

We are talking of a database of at least 100gb... imagine the time I
need to backup, restore, delete data and shrink it.
Thanks anyway.

Yes we do back it up, but to tape and the other SQL (the one i'm using)
does not have tape device.
As the app I'm using need the info I need to keep my db as updated as
possible.

Use Enterprise Manager to generate the creation script for the database.
Right-click the DB, select All Tasks > Generate Scripts.

--
David Portas
SQL Server MVP
--

[quoted text, click to view]

You mean the object IDs are referenced in the application?! I'm sorry, but
that just seems unbelievably stupid! I would tell the customer to dump that
application ASAP.

I think the best you can do is try to run the creation scripts in the right
order in an attempt to generate the correct IDs. You'll have to query the
system tables for the current state and then try to reproduce it in a
script. Use that information to fix the application code if you are able.

--
David Portas
SQL Server MVP
--

You could restore the entire database then delete all the data, shrink it
and then back it up again. Once you've done that backup you'd have it for
future reference. Maybe someone else can come up with something better but I
can't think of another way to preserve the IDs.

--
David Portas
SQL Server MVP
--

[quoted text, click to view]

You mean you don't back it up anyway? Why is time a factor for this? How
often do you expect to have to do it?

--
David Portas
SQL Server MVP
--


[quoted text, click to view]

Vassago (iciruzzi@gmail.com) writes:
[quoted text, click to view]

I can tell you that this time is only a mere fraction of the time you
could waste on your other dead idea. And, I don't really see why you
would need to this multiple times. Do it once, and then create an
empty template database to work from.

The total time for this operation is less than 24 hours.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at