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

In addition to the above answers, I'd like to suggest that (for future projects, at least) you don't have you master database design in the database itself.

One way to achieve this is to either simply maintain your database tables, procedures etc as 'CREATE' scripts from day one, and devise a master script that will pull all of the individual scripts together for deployment to a database of your choosing.

A more sophisticated solution is to use something like Visual Studio Database Edition (Probably too pricey, if your comments are anything to go by) which allows you to treat each database object as a node in a project, whilst still allowing the whole thing to be deployed with a few clicks.

The explanation of both of these options is over-simplified, as there are a lot of other issues - versioning, data migration etc - but the main principle is the same.

Once you've extracted your script - using one of the other answers - you may want to consider using this script as the basis for your 'master'.

Just keep the 'design' out of the database - that's purely for 'implementations'.

Try to think of the process as similar to developing code - the source and the binaries are kept separate, with the latter being generated from the former.

Answer 2

So, I assuming that you cannot install SQL Server Management Studio. Not even the free version. If this is a onetime thing, I would install the red gate tools. The trial version is fully functional for 14 days. Do your work, then uninstall. You might also check out http://www.xsqlsoftware.com/, they have similar functions as Red Gate. If your database is small, then they have a free option for you. I also like http://www.apexsql.com/. Use the trial of one of these and then try again to convince your boss to buy one.

Answer 3

The others are correct, but in order to create a full database from scratch, you need to create a 'device' in SQL before you run the create tables, and procedures scripts...

Use ADODB, since just about every (if not every) Windows box has it installed to execute the script.

Hell, you could even write a vbScript that executes to build your entire database. Any domain tables you have, you need to remember to turn on the identity insert before you add the items to the DB.

I'm open to source code sharing if you need it. I had to write the very same thing a couple years ago, and ended up with 3 scripts, one that created the device, then the tables, then the procedures/triggers and domain data. I wrote the code to parse the default script and allow the user to name his own database, and logins, etc.. You may not have to go that far.

Hope this helps at all.

Answer 4

Michael Lang, when you right-click on the database and choose to create a script, there are several options boxes that you will need to click in order for everything to be generated. 2005 is much easier in this respect, but 2000 can do this, you just need to select the proper options.