I have two databases one is having master kind of tables and other will have transaction data. I should create a new database with a structure of an existing database to my clients. Here is the scenario..

If the particular db will comes under transaction type then structure of an existing db should copy If the particular db will comes under master type then structure of an existing db along with data should copy

i.e Transaction--> Only Schema
Master--> Schema+Data

I tried with Generate script, Is there any way to automate this process instead of manually generating script for both schema and Schema+Data for every copy of a new db.

Please suggest.

  • You might consider doing a backup/restore for your "Master" database. That way you get Schema+Data without scripting at all. Jan 28, 2015 at 19:33

1 Answer 1


Create two template databases - once - one with the schema, and one with schema + data - populated with the manual generate scripts method.

Take a backup of each.

When you want to create a new database, instead of a manual CREATE DATABASE and then manual population, restore the backups as new names and the WITH MOVE options to move the data/log files.

Refresh the templates, obviously, every time you make meaningful changes to the schema and/or data that you want used for future, new databases.

You could reduce this work by putting just the schema in model, then CREATE DATABASE would create an empty database with your schema, and you'd only need to do something for the one with the data. Or vice-versa. But note that this will affect all future databases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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