The Joel on Software Discussion Group (CLOSED)

A place to discuss Joel on Software. Now closed.

This community works best when people use their real names. Please register for a free account.

Other Groups:
Joel on Software
Business of Software
Design of Software (CLOSED)
.NET Questions (CLOSED)
TechInterview.org
CityDesk
FogBugz
Fog Creek Copilot


The Old Forum


Your hosts:
Albert D. Kallal
Li-Fan Chen
Stephen Jones

Running scripts to recreate database

Hi
I am using a tool,which generates scripts(.sql) for all database objects-one file per object(for SQL 2005).Now,I want to run all these scripts to recreate database.What are the options I have? Any open-source tool? It seems to me,I will have to first run creation of tables scripts,then fk creation and so on.I don't want to use query analyzer or management studio.
shakal
Friday, February 22, 2008
 
 
Albert Tollkuci Send private email
Friday, February 22, 2008
 
 
SQLCMD
JohnEric Send private email
Friday, February 22, 2008
 
 
I'm not up to speed on SQL 2005, but in 2000 this could be easily scripted in a few minutes with a batch file (or VBScript, or PowerShell or <your scripting language of choice>) and calling OSQL to execute your individual scripts:

(Crap pseudo code)
For Each SQLScript in MySQLDirectory

    'Call OSQL to execute the SQLScript file

Next
Sgt.Sausage
Friday, February 22, 2008
 
 
www.modelright.com

the version 3 (MR3), supports SQS Server 2005/2008. While it is a database modelin/design tool, it does have a built-in editor that will execute SQL.
anon
Friday, February 22, 2008
 
 
use sqlcmd.exe, or it's actually not that difficult to create a simple parsing tool that can grab batch statements from a file and execute them against an open DB connection, if sqlcmd is not an option.

I've used both, having a build call SQLCMD to prepare and populate databases, and a simple SQL script running object to do database preparation for things like unit or integration tests within code.
Dan Fleet Send private email
Friday, February 22, 2008
 
 
It is more than executing statements. His issue is that the statements need to be executed in the right order.
anon
Friday, February 22, 2008
 
 
\. file.sql
lemon obrien Send private email
Friday, February 22, 2008
 
 
"His issue is that the statements need to be executed in the right order."

Why's this a challenge?  Just run them in the right order .. he has the set of scripts, he should be able to put sequential calls to SQLCMD.EXE in a batch file, in the proper order.

Or is he looking for some tool with the smarts to import a pile of SQL scripts in random order, calculate the interdependencies, and then output a series of SQL statements in an order that will successfully execute given these dependencies?
Dan Fleet Send private email
Friday, February 22, 2008
 
 
"Or is he looking for some tool with the smarts to import a pile of SQL scripts in random order, calculate the interdependencies, and then output a series of SQL statements in an order that will successfully execute given these dependencies?"

Yes, I think it is part of the problem. If you have hundreds/thousands of statemens, can be an issue.
anon
Friday, February 22, 2008
 
 
My understanding was that SQL's own script generator would output the FK stuff in dependency-order.

Failing that it should be possible to run all non-FK scripts first (obviously Create Table before running the Create Index script - I presume the scripts have some sort of file-naming convention that enables that, otherwise you are hosed!)

Then run the FK scripts last (after importing the data would remove the need to import the data in a FK-aware manner), and re-run the FK scripts until no new FKs are created (or output each FK-create that generates an error to a "run again" folder, and just re-run those until there are no more).  I think that will deal with circular FK definitions, but putting a limit on the number of iterations would be prudent.

We append every DDL statement into a "patch file", as they are created in DEV, so that we can run that script against QA / Test / Production / etc.  If you can adopt that discipline it removes the time & aggro of trying to create a bullet-proof script by reverse engineering etc.
Krispy Send private email
Saturday, February 23, 2008
 
 
ANT/NAnt script calling SqlCmd is one way.
Another is to write a small program that uses SMO objects.
xampl
Saturday, February 23, 2008
 
 
said it a million times before, will say is again and againt

DBGhost (www.dbghost.com)

Not affiliated, just a user for 2 years and extremely satisfied.
... Send private email
Sunday, February 24, 2008
 
 
I have explored sqlcmd,and it seems a nice tool.But,I am not able to figure out how to run it,to execute a .sql file.
From command prompt,I am able to connect to the sql server,and execute sql statements,using sqlcmd
But,my plan was to create a .sql file,which will invoke sqlcmd to connect to server,and execute .sql files.But,i am unable to connect to server using :connect command.Any ideas?
shakal
Monday, February 25, 2008
 
 
JohnEric Send private email
Monday, February 25, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz