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.)

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 (similar to in postgresql)? Ideas? Thanks, -Matthew_53

Just thinking out loud really, but you could install a second SQL Server instance (possibly on the same server - limiting the memory available to the second instance, if appropriate) and maintain the model database within that instance.

Your database creation process would then be to create the database on the new instance, detach, then attach to the new instance - you could do this by running just one script per instance or even by setting up a linked server and performing the actions with a single script. If you set the default Data and Log folders to the same folders as the existing instance then you wouldn't even have to move any files around.

If you lock down the security on the second instance you can limit who has access to modify the model database.

Chris

|||Better yet, why not just create the template/model database on the one instance, then detach it and store the data and log files away for safe keeping? Then any time you wanted to clone it, it would only be a matter of duplicating these files into the appropriate data directories, and attaching them with the desired database name.

You could also achieve the same effect using a backup/restore, as long as you're using the WITH MOVE clause during the restore. This way, you could leave the template database online and maintain it easily, at the expense of increased cloning times.

Either approach could be made pretty turn-key with some scripts containing an appropriate mix of BACKUP, RESTORE, sp_attach, sp_detach, xp_cmdshell (to copy files), etc.