Microsoft SQL Server forum - recreate system tables

Author

recreate system tables

Vassago

2005-07-28, 11:36 am

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

Vassago

2005-07-28, 11:36 am

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.

David Portas

2005-07-28, 1:38 pm

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
--

David Portas

2005-07-28, 1:38 pm

> I have an application that has the Id of the objects in the sysobjects
> table and I need to know it's name.

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
--

Vassago

2005-07-28, 1:38 pm

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?

David Portas

2005-07-28, 1:38 pm

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
--

"Vassago" <iciruzzi@gmail.com> wrote in message
news:1122572272.797029.108370@o13g2000cwo.googlegroups.com...
> 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?
>

Vassago

2005-07-28, 1:38 pm

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.

David Portas

2005-07-28, 1:38 pm

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
--

"Vassago" <iciruzzi@gmail.com> wrote in message
news:1122574013.104947.297100@o13g2000cwo.googlegroups.com...
> 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.
>

Vassago

2005-07-28, 1:38 pm

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.

Erland Sommarskog

2005-07-28, 8:24 pm

Vassago (iciruzzi@gmail.com) writes:
> We are talking of a database of at least 100gb... imagine the time I
> need to backup, restore, delete data and shrink it.

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
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links