Is there a “poor man's” alternative to RedGate

Question

I'm in a situation where I would to generate a script for a database that I could run on another server and get a database identical to the original one, but without any of the data. In essence, I want to end up with a big create script that captures the database schema.

I am working in an environment that has SQL Server 2000 installed, and I am unable to install the 2005 client tools (in the event that they would help). I can't afford RedGate, but I really would like to have a database with identical schema on another server.

Any suggestions? Any simple .exe (no installation required) tools, tips, or T-SQL tricks would be much appreciated.

Update: The database I'm working with has 200+ tables and several foreign-key relationships and constraints, so manually scripting each table and pasting together the script is not a viable option. I'm looking for something better than this manual solution

Additional Update Unless I'm completely missing something, this is not a viable solution using the SQL 2000 tools. When I select the option to generate a create script on a database. I end up with a script that contains a CREATE DATABASE command, and creates none of the objects - the tables, the constraints, etc. SQL 2005's Management studio may handle the objects as well, but the database is in an environment where there is no way for me to connect an installation of Management Studio to it.

Answer 1

As you mentioned in SQL Server 2000 the command to use is:

While in Enterprise Manager select the database you want to script objects from and then Right Click and select All Tasks -> Generate SQL Scripts...

While in the Options Pane it is handy to select option Create one file per object it that way you can store each object in source control separately.

So then whenever you do some updates on a tableA you can check it out in source control to let others know that you work on it and the after you finish you can script that single object and save check it in.

To script a single object you can you the same option All Tasks -> Generate SQL Scripts... and then select just that one object.

The only problem with that approach is when you want to restore the complete database you need to take care of dependent objects in the sense that the top level object must be restored before the ones dependent on them.

Ironically when you script the whole database to one file the objects are not ordered in terms of dependency but rather based on the creation date. That leads to errors when you try to use it to restore the complete DB.

We ended up creating batch file that would create each object separately calling "osql "

And that all worked pretty well at that time

Now we use SQLCompare Pro and it safes us from or that hassle, but if you do not make release frequently you can live without it.

Answer 2

Microsoft Database Publishing Wizard (in Visual Studio 2008).

Answer 3

If you're looking for a command line version, Sql Server Hosting Toolkit has worked for me in the past.

Answer 4

If it is one-off operation and you do not fancy ordering the object scripts yourself just download a free trial version of RedGate SQL Compare.

It is 14 days full working version so it will do the entire job for you – it is completely legitimate solution. And you will have all the scripts for the future use with your manual scripting. Anyway I am pretty sure that if you find out how handy the tool is you will buy it in some later stage and that is what they probably hope for by offering fully working trial. It somehow worked in that way in my case.

Just be aware that once the trial expires it affects all the tools so make sure to make the most of it.

Answer 5

I have used this program from CodeProject successfully many times before. Not only does it script out the schema, it can (optionally) script out all the INSERT statements you will need for recreating the database.

I've used it against SQL Server 2000 in the past. Obviously if you have a million rows in a table it might not be a good idea to script out the contents but it's a really neat tool actually to make a series of SQL scripts to replicate a database.

Answer 6

Run SQL Server Management Studio, right click on the database and select Script Database as > Create to > file

That's for SQL Server 2005. SQL Server 2000 Enterprise Manager has a similar command. Just right-click on the database > All Tasks > Generate Scripts.

EDIT: In SQL Server 2005, you can select "Database" in the object explorer pane and select several databases in the details pane. Then, right-click on your selection and "Script Database as > Create to > file". This will cause it to put them all into one script and it will include all tables, keys, stored procedures, and constraints.