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?
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:ModelDBsCalifornia.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.
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.