Back when I worked for Promod (one of my first client), the dev team had a wonderful toy : each monday, the database we used for building applications was updated to reflect the last data from the live database.Since then, I have always tried to mimic this feature for every project that I worked on.
The trick was not really pretty : every monday, a database backup was restored from live environnement to the dev one. Then a sometime really long script/sql batch was launched to update the schema (every change that we made to the database had to be put in script in a special folder, and all those scripts were executed every monday)
This week, I really wanted to get this thing on a new project but the vbscript-based tool I've used many times before didn't worked for some reason, so I have rewritten the whole thing with Powershell.
The script is split in a few parts :
- first we have to ensure that no one is locking the database. The dirtiest of tricks is used here : I just shutdown and restart SqlServer, remembering to restart all the dependent services (such as Sql Server Agent) that were running before the restart
- a restore database is launched thru osql [Updated 05-mar-2007 : As many of you may have guessed, the actual copy of the .bak from the backup server should be done before restarting sqlserver - I didn't put it before in this post only for readability.]
- a Team Foundation Server-based refresh of the script directory is then triggered
- for each .sql file in the aforesaid directory, powershell just run osql with this file
With less than 10 lines (I had to define the scriptdir & tfpath variables, and to add a little error handling), this script replace an about-150-lines vbscript file, that's pretty amazing !