Database Cloning

[George Shubin]

Using SQL Server 2005 Express.

I need to periodically duplicate an entire database and rename it. Most
tables are empty but some are to be prepopulated. I was thinking of having
a "template" database called perhaps "EmptyDatabase", and then copy that
into a freshly created database with a new name.

Has anyone coded anything like this?

Thanks.

GS

[Hugo Kornelis]

On Tue, 21 Nov 2006 10:39:21 -0800, George Shubin wrote:

>Using SQL Server 2005 Express.

I need to periodically duplicate an entire database and rename it. Most
tables are empty but some are to be prepopulated. I was thinking of having
a "template" database called perhaps "EmptyDatabase", and then copy that
into a freshly created database with a new name.

Has anyone coded anything like this?

Hi George,

One thing you can do is add these standard tables to the "model"
database. Each time you create a new database, it is created as a copy
of the "model" database, so each new database will have those tables.

If you don't want these tables in ALL new databases, then I'd create one
database with the required tables and make a full backup. You can then
create copies of that database by using RESTORE DATABASE with the WITH
MOVE option.

--
Hugo Kornelis, SQL Server MVP

[George Shubin]

Thanks, Hugo.

Putting the tables in there just might be the ticket. I didn't know that
was one of the purposes of the Model database.

Thanks for the suggestions.

GS
"Hugo Kornelis" <hu**@perFact.REMOVETHIS.info.INVALIDwrote in message
news:1e********************************@4ax.com...

On Tue, 21 Nov 2006 10:39:21 -0800, George Shubin wrote:

>>Using SQL Server 2005 Express.

I need to periodically duplicate an entire database and rename it. Most
tables are empty but some are to be prepopulated. I was thinking of
having
a "template" database called perhaps "EmptyDatabase", and then copy that
into a freshly created database with a new name.

Has anyone coded anything like this?

Hi George,

One thing you can do is add these standard tables to the "model"
database. Each time you create a new database, it is created as a copy
of the "model" database, so each new database will have those tables.

If you don't want these tables in ALL new databases, then I'd create one
database with the required tables and make a full backup. You can then
create copies of that database by using RESTORE DATABASE with the WITH
MOVE option.

--
Hugo Kornelis, SQL Server MVP

[Hugo Kornelis]

On Fri, 24 Nov 2006 10:57:21 -0800, George Shubin wrote:

>Thanks, Hugo.

Putting the tables in there just might be the ticket. I didn't know that
was one of the purposes of the Model database.

Thanks for the suggestions.

Hi George,

The main purpose of the model DB is to give you an easy way to insure
that all new datbases are created with the same options. But there are
also many DBAs that stick common "utility" tables in there, such as a
numbers table or a calendar table. Your use is less common, but still
correct use of the model DB.

--
Hugo Kornelis, SQL Server MVP