Multiple "model" DBs -- or db creation 'profiles'

Question

I know you can't actually have multiple 'model' DBs on the same server instance. BUT, I'd like to have something almost like it. Basically, we have a business model where each customer is its own DB, and these DBs are all very similar in structure & definition (different in actual data/record content of course). And then we have some 'global' DBs as well that are accessed and joined-to from each customer-DB.

So when we obtain a new customer, we need to create their DB. We want the DB to be pre-populated with certain tables/procs, and certain pre-seeded data that is pretty consistent (one could say duplicated/redundant - yes, that'd be fair, but it's also very customizable and doesn't necessarily remain redundant after we start customizing that client's configuration).

Our initial solution was to build all of that into the [model] DB. However, it's tailored to a particular kind of client - say, California. Now we need to model, say, a Michigan client, and create a number of new DBs based on that. So this is a problem because we need to kill our [model] and put all that CA model-data/structures somewhere else.

My thought was, let's just create a 'template' DB for each state, and when we need to create a new client DB, we just restore-from template's full-backup file (doing the Rename in the process of course). Is this the thing to do? Or is there a better way?

Answer 1

I agree I would create an example of each type and then back each up. Once they are backed up you can drop the examples. Then when you need a new "California" type you just run something like this:

RESTORE DATABASE <NewDBName> FROM DISK = 'x:\ModelDBs\California.bak'
WITH FILE = 1,
MOVE 'CaliforniaDataFile' TO 
    'E:\Data Files\<NewDBName>.mdf',
MOVE 'CaliforniaLogFile' TO 
    'F:\Log Files\<NewDBName>.ldf';

You could even create a template to generate most of the script for you.

The other option is to create "creation" scripts for each. Create a blank DB and run your creation script for the appropriate type. This can be automated to a large degree but I still like the backup/restore method better.

Answer 2

I would.

That's what we do in our environment.

We get requests from the application admins requesting that we "copy" from a template.

I just backup the template, and restore the database renaming the files and the database in the restore.

Works pretty well for us.