Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Good stuff, thanks guys. But, did I forget to mention something? Oh yes I did. Our DB schema is always changing! Yep. So we have a little tool that we use to "run SQL everywhere" when we need to make a structural change, and it loops thru the client DBs and makes said change. These models/templates would need to be 'online' so that they could be accessed by this run-everywhere tool. –  NateJ Feb 17 at 22:17

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.