create new db from existing one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

create new db from existing one

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-09-26 : 09:08:34

Hello,

I have a need of creating several dbs from an existing one. It is kind like using the one I have as a template. The newer ones will have a different name, and will have no data. They will all reside in the same server.

So, how to create myClientAdb, myClientBdb... from myTemplateDB? And, yes I am looking for easiest way and possibly repeatable way.

Thanks!

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 09:13:41

Make a "build script" from the existing database;

Create a new database

Run the build script.

Or:

Empty the existing database of all its data

Back it up.

Restore the Backup to a new database each time you want one.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-26 : 09:39:12

quote:
Originally posted by Kristen

Make a "build script" from the existing database;

Create a new database

Run the build script.

Or:

Back it up.

Empty the existing database of all its data

Back it up.

Restore the second Backup to a new database each time you want one.

Kristen

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 09:51:54

I did think that, honest, but it made the response too complicated ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-26 : 10:04:04

quote:
Originally posted by Kristen

I did think that, honest, but it made the response too complicated ...

Well. I modified your answer as option2 was not exactly equivalent to option1

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 10:25:48

Actually I was trying to test the OPs disaster recovery procedure

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-09-26 : 11:06:40

Thanks.

But how to empty all those tables? I can think of looping through them and truncate each as one way.

Any other way?

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 11:15:20

"I can think of looping through them and truncate each as one way."

Yes, that is the way. There are discussions on here about the best way to do that, taking into account FKs and so on. (e.g. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341)

You would probably be better off with the Script route - even if it was only to build an empty database that you then Backup & Restore [as a template] to create your new database.

Kristen

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-09-26 : 11:37:18

I have just went through the the scripts I created and I think this will work. I am going to run them later, and report back.

Here is how I got my two scripts.

In management studio,
Right click my "template", Script db as...Create to...Query editor.
Then rreplace all "template" with the myclientA in the script.
This will give me an empty db with all db conf settings, log files, etc.

Then Right click, Task, Generate Scripts...script all objects in selected db. Also replace db name here.

I think this script will create all objs but leave out the data, because I did not see any part that copys data.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-09-27 : 10:39:03

They worked.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources