September 15, 2008 at 11:08 am
Hi,
I'm writing a series of scripts that will make a copy of my database. So far I have all of the scripts written to copy specific subsets of data from the original tables into the new database's matching tables. What I need now is a way to create an empty version of the original database so I have somewhere to copy the data to.
In management studio you can right click the database > tasks > generate scripts and it will script the whole thing out for you. I need to be able to generate the script at the time the copy is required because the schema frequently changes. Is there a way to script this behavior out to run dynamically?
September 15, 2008 at 11:15 am
You might be able to if your modifications are in some sort of source control application. Are they?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 16, 2008 at 4:00 am
You could create a copy of an existing database that has the structure you want to put your subset of data into and then use sp_msForEachTable to delete all rows in all tables in that copied database. The way I do things is to have a base .bak file for an old version of our products and a host of .sql files to update from version to version. Every night MSBuild restores the .bak and then applies all .sql files under source control to it. These sql files both update the structure and add static data. I find this is a good way to test upgrade scripts on a daily basis rather than running into problems when a client wants to upgrade. If you really need to generate a structure script dynamically at run time you're going to have to use the functions in the Microsoft.SqlServer.Management.Smo namespace in some .net language as there is no T-SQL solution (that I'm aware of anyway) to producing the script. The smo stuff is very easy to use however, and is very powerful.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply