Create from template database?

I have a database that needs to be created over and over (with dozens of pre-populated tables & procedures) for various users of a software application. These databases must remain distinct. Currently, the table/procedure data is maintained in Create and Insert scripts (approx 15,000 lines total) that we run as needed, but maintaining these scripts in unwieldy at best.

Ideally, I would like to create each new database from a template database. This way, we could forget the scripts entirely, and make future modifications directly to the template, and have them automatically bubble into all future databases created from it.

I know this is how the "model" system database works, and think it is great! Make some change in model, and every new database is created with that change. Perfect, except that we cannot guarantee that our software application will be the only one using some instance of SQL (SS2K5), or that our databases will be the only ones inheriting from model.

So, instead of having EVERY new database inherit from model, is there a way to specify that only certain databases inherit from 'model' while others inherit from 'custom_model'? Is there a way to specify a database to use some template in the create process (maybe similar to in postgresql)? Ideas? Thanks, -Matthew_53

Unfortunately, there is no 'conditional' way to use multiple [Model] databases.

However, you can easily create your 'template' database, and when you need another database derived from that template. In the object explorer of SSMS, right-click on the template database and select [Tasks], then select [Copy Database...]. Follow the steps throught the Copy Database Wizard.

|||Right, but is there a way to do this programatically, from within our application, whenever we need to generate a 'new' database?|||Programmatically, you could DETACH the template database, then xcopy it with a new db name, and then re-ATTACH both of them. (Actually, unless you need to continually 'tweak' the template database, it doesn't even need to be attached. Just copy the *.mdf file to a new name and ATTACH the new database.)