Restoring Datebase (Structure only)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Restoring Datebase (Structure only)

Author

Topic

510SX
Starting Member

5 Posts

Posted - 2006-11-27 : 17:33:14

hi noob here to ask a question.
I am trying to restore a database to a new box, but I only want to restore the tables, sp, diagram... everyththing but the data. Is it possible?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-11-27 : 17:38:41

Instead of restoring the database, how about script out the database and all it's objects. If you are in sql 2000, use Enterprise Manager | tools | Generate Sql Script.

Then apply the script to where ever you want. That way you get the objects without the data.

Be One with the Optimizer
TG

510SX
Starting Member

5 Posts

Posted - 2006-11-27 : 18:52:22

Ah, thank you. It works!

510SX
Starting Member

5 Posts

Posted - 2006-11-28 : 18:08:20

May be I spoke too soon. After I ran the script, I got rid of all the data, but also broke the diagram(relationship?). where is the diagram is stored? Can I back up the diagram only?

swatib
Posting Yak Master

173 Posts

Posted - 2006-11-29 : 04:44:11

Database diagrams are stored in the 'dtproperties' table within the database. So, database diagrams can be transferred to a different database, by transferring the contents of this table.

For example , run the following query to transfer the diagram named 'MyTableDesign' from 'pubs' database to 'northwind':

INSERT northwind..dtproperties
SELECT objectid, property, value, lvalue, version
FROM pubs..dtproperties
WHERE objectid =
(
SELECT objectid
FROM pubs..dtproperties
WHERE value='MyTableDesign'
)

Make sure, the tables referenced by these diagrams already exist in the target database, or else these diagrams won't show up in the target database.

If the target database already has a diagram with the same 'objectid', you will see unpredictable results, when Enterprise Manager displays the diagrams. In that case, you might want to explicitly specify a unique value to the 'objectid' column while transferring rows from the source 'dtproperties' table.

Njoy Life

510SX
Starting Member

5 Posts

Posted - 2006-11-29 : 11:02:21

Thanks swatib

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources