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.