CODEWITHSUNDEEP
sqlsql-server-2008
pjbrown88
pjbrown88

Reputation: 55

How do I reconstruct a database schema from hundreds of separate scripts?

I requested that a client send me a copy of their current MS SQL database. Instead of being given a database backup, or small set of scripts I could use to recreate the database, I was provided with hundreds upon hundreds of individual SQL scripts, and no instructions on the order in which they'd need to be run.

The scripts cannot simply be executed in one batch operation, as there are foreign key dependencies between tables. It appears as though they've limited these scripts to creating a single table or stored procedure per script.

Normally, I'd simply ask a client to provide the information in a more usable format, but they're not known for getting back to us in a timely manner, and our project timeline is already in jeopardy due to delays on their end.

Are there any tools I can use to recreate the database from this enormous set of scripts?

Upvotes: 1

Views: 273

Answers (5)

David Atkinson
David Atkinson

Reputation: 5899

If you set your folder of scripts as a data source in Red Gate SQL Compare, and specify a blank database as the target, it should allow you to compare and deploy to the target database. This is because the tool is able to read all SQL creation scripts recursively from the folder you specify. This is available as a fully functional 14-day trial, so you can easily test it in your scenario.

http://www.red-gate.com/products/sql-development/sql-compare/

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

Building on Gordon.
Split them up into one table each.
Count the number of FK and sort starting with least first.
Then remove the scripts that run as Gordon suggests.
Another potential problem is that is creates the table and fails on the FK and leaves the table.
You come back later to create the take and the table is already there so it fails.

If you parse them out with Table FKs
Start with Tables with no FK in a list
Then loop thru Tables with FK
Only add to the List if all the FK are already in the List.

If you know .NET then a class with string property table, sting property script, and a property List String of FK property names.
They should parse out pretty clean regex.

Upvotes: 0

Khalefa
Khalefa

Reputation: 2304

You can remove all the foreign keys constraints. Then, organize the scripts so that it first creates all the tables, then add back all the foreign keys. Finally create indexes.

Upvotes: 0

Dave Markle
Dave Markle

Reputation: 97671

The quickest (and by far the dirtiest) way of (maybe) doing this is to concatenate all of the scripts together, ensuring that you have a GO statement in between each one. Make sure there are no DROP statements in your scripts, or this technique won't work.

Run the concatenated script repeatedly for... I don't know, 10 or so iterations. Chances are you will have their database recreated properly in your test system.

If you're feeling more rigorous, go with Gordon's suggestion. I'm not really aware of a tool which will be able to reconstruct the dependencies, but you may want to take a look at Red-Gate's SQL Compare, which you can get a demo of for free, and can do some pretty magical things.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This may sound a bit arcane, but you can do the following, iteratively:

  1. Put all the scripts into a list of "scripts to be run"
  2. Run all the scripts in the "to be run" scripts
  3. Remove the successful runs
  4. Repeate 2-3 until no scripts are left

The scripts with no dependencies will finish in the first round. The ones that depend on them in the next round, and then so on and so on.

I would suggest that you operate all this from a metascript, that uses a database table to store the names of the available scripts.

Good luck.

Upvotes: 5

Related Questions