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
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Best tool to script a database

Author  Topic 

SamC
White Water Yakist

3467 Posts

Posted - 2004-07-09 : 18:45:42
What tools are out there to script an existing database?

Sam

JCamburn
Starting Member

31 Posts

Posted - 2004-07-09 : 19:22:00
SQL Server has its own scripting tool that works perfectly. From Enterprise Manager, right click on the database and select

All Tasks -> Generate SQL Script...

If you want to script insert statements for all the data in your database, I believe that RedGate's SQLCompare can do it for you.

Hope this helps.
Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-09 : 19:34:33
Hi Sam,

I am not sure about the best tools. But the price is certainly right on these. :)

Nigels script looks pretty good:
http://www.nigelrivett.net/DMOScriptAllDatabases.html

Also, here is a neat looking .NET app which also uses SQL-DMO and the app can be command line driven to schedule backups and also integrates with VSS:
http://www.codeproject.com/cs/database/DBScriptSafe.asp

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-09 : 20:16:16
I use the Generate SQL Script wizard in Enterprise Manager (one of the few things I do in there). RedGate's SQL Compare is an excellent tool, but I use it for comparisons only.

Tara
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-07-10 : 01:51:26
Does Enterprise Manager Generate Script fall short for you?

I can think of two scenarios (where an Entity Modeling tool would probably do better)

1. generate scripts for change from Version 1 to Version 2
2. output a script for Oracle rather than SQL Server

Kristen
Go to Top of Page

JCamburn
Starting Member

31 Posts

Posted - 2004-07-10 : 02:00:12
Kristen:

1. Enterprise Manager can easily be used to generate incremental scripts to change from Version 1 to Version 2, as you put it.

2. This is a valid point if you care about the portability of your database. Personally though, I believe such portability is highly over-rated.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-07-10 : 02:25:32
1. I need a hint please!

2. Absolutely. We started off supporting several DBs, and totally un-optimised. Now we are heavily optimised on just MSSQL.

Kristen
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-07-23 : 05:37:09
Is there a way to generate the script using tsql?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 06:06:27
http://www.nigelrivett.net/DMOScripting.html

Kristen
Go to Top of Page

rlahoty
Starting Member

11 Posts

Posted - 2004-07-23 : 10:51:47
You can also use scptxfr.exe utility that comes with SQL Server (that you can find in C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade; it may be different for you) directory. It is a command line utility and does a nice job of scripting objects of the database.
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2004-07-23 : 12:10:25
If you need to script the data, as well as the schema

<Shameless self promotion>
You can use SQLDataScripter. Download at www.clrsoft.com.
</Shameless self promotion>


-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.
Go to Top of Page

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-07-23 : 13:56:04
I've used the SQLDataScripter, and though I haven't pushed it to any big limits, it does its job quite well. Easy to use and quick, and handles small obstacles such as scripting identity columns easily. Hey you can always bank on me for a testimonial, Chad! Good job.

OS
Go to Top of Page
   

- Advertisement -