Script package to recreate a database from a control source

Question

As far as I understand, if I want to get my database under the initial control, I need to check the change scripts for each change, and then run them from some version to get the correct DB.

I am trying to make a batch file that will also be checked, which allows other developers on the team to rebuild the database locally without any problems. I believe sqlcmd is a way to achieve this. I have a setup to list all the files in the .sql file directory and execute sqlcmd for each.

My question is: who did this before, and do you have any advice on the best way to do this? Is there a way I intend to do this in the best way, or is there a better way?

Hope this is not too vague.

Thanks in advance,

Martin.

Answer 1

If you use an ORM tool such as NHibernate, you will save yourself by having database modification scripts because ORM can recreate a database from it by matching files (which are under source control).

This is a pretty simple way to have a suitable db version for each revision.

Then, I also recreate the entire circuit when I run the tests to make sure that I have a consistent state.

I recently wrote about this: http://www.tigraine.at/2008/10/30/sourcecontrol-and-databases-when-orm-comes-in-handy/

I also had a project in which there were Sql update scripts, and we just had a built-in small helper (very simple) that opened the folder, sorted all the scripts (we will call them 1 - foo.sql, 2 - bar.sql) and performed them in accordance with the database.

If the developer had a new script, he simply added it to the end (34 - bla bla.sql).

Answer 2

I developed a small utility that helps me modify my tables, SPs, triggers and views, exporting them to text files: gljakal Sql Exporter . It has GUI mode and command line mode (so I can use it in batch files).

Powered by SQL 2005+.

Answer 3

One thing to know about is that you need to create objects in a dependent order. Therefore, you cannot just iterate over files.

As a result, we got a batch file in which all objects will be listed in the order of dependence

In fact, we had 2 batch files called createDBObject.bat:

:: Parameters Required: :: %1 UserID :: %2 Password :: %3 Server :: %4 Database :: %5 file with scripted object :: echo. >> CreateDBObjectsLog.txt echo %5 >> CreateDBObjectsLog.txt osql -U%1 -P%2 -S%3 -i%5 -d%4 -n >> CreateDBObjectsLog.txt echo * %5 

and then another with a list of all db objects:

 :: Parameters Required: :: %1 UserID :: %2 Password :: %3 Server :: %4 Database :: echo object in %4 database on %3 server echo Please Wait ... if exist CreateDBObjectsLog.txt del CreateDBObjectsLog.txt call createDBObject.bat %1, %2, %3, %4, ScriptedTable1 call createDBObject.bat %1, %2, %3, %4, ScriptedTable2 ... call createDBObject.bat %1, %2, %3, %4, ScriptedTableN call createDBObject.bat %1, %2, %3, %4, ScriptedView1 call createDBObject.bat %1, %2, %3, %4, ScriptedSP1 etc 

Now we use SQL Compare Pro , which automates all these tasks.

You can also check the relevant question: Is there a RedGate alternative for "poor mana" to script the entire database schema?

Answer 4

If your company is willing to invest in some good tools, you should check out SQL Compare Pro . It is well suited for automating / simplifying the tasks you describe. They have a 14-day free, full, trial version, so you can test it before investing.

Answer 5

Interestingly, the OR mapping structure can create a circuit — I haven't thought about that. I actually use LINQ To SQL, but I believe that the schema and database can still be recreated with a call to CreateDatabase ().

Has anyone else used this approach to managing a database source through LINQ To SQL?