Database Cloning

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.

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

Quote:

Originally Posted by

>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|||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" <hugo@.perFact.REMOVETHIS.info.INVALIDwrote in message
news:1eacm2900qqojkaim4npoftlibmasratfm@.4ax.com...

Quote:

Originally Posted by

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

Quote:

Originally Posted by

>>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

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

Quote:

Originally Posted by

>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

Database Change Scripts

Please forgive me if I'm in the wrong category, and I know this is a duplicate post...

How do you deploy changes to a database? Is there something you can run to compare the two versions and create a script to do this? I've heard about the DTS package (now DTSX I believe) but am not sure how to set one up. If anyone could point me to an article or something that might clear things up, it would be greatly appreciated. Thank you!

There are 3rd party tools out there to compare 2 databases / schemas and produce scripts to synchronise them - in both directions. I, myself, use redgate software's SQL Compare (for comparing db design) and Data Compare (for comparing data in tables). However, other products exist such as Innovartis' DBGhost & Quest's Toad (I use it for Oracle but it might also be in their SQL Server solution).

All the companies above have evaluation copies for download from their respective websites.

These tools are a godsend when a release is not performing as expected and time is critical.

|||Thanks, very helpful. I'll check out some of those products and give 'em a try. Too bad SSIS doesn't do that for you.|||

Check out SQL Effects too, as they have free version (Communition Edition)

http://www.sqleffects.com/

Database Acting Strange

MY DATABASE FOR SHOPPING CART IS ACTING STRANGE IT HAS AUTOMATICALLY DESTOYED ALL PRIMARY KEYS. AND HAS CREATED 4 DUPLICATE RECORDS FOR EACH RECORD SO AT THIS STAGE I HAVE AROUNG 15000 RECORDS IN EACH TABLE IT MEANS 15 TIMES 4 60000 RECORDS. CAN ANYONE UPDATEME WHY THIS HAS HAPPENED AND WHAT CAN BE DONE TO DELETE EXISTING DUPLICATES. I NEED FAST REPLY IN THIS MATTER AS THIS IS URGENT. IF POSSIBLE KINDLY WRITE QUERIES TRIGGERS FOR FUTURE PROBLEMS AS WELL AS THESE DAYS I AM SICK SO MY BRAIN AINT WORKING IN THIS MATTER. URGENT URGENT URGENT THANKS ALOT I HAVE PREVIOUSLY POSTED AND HAVE GOT A GOOD RESPONSE. AND HOPE A GOOD ONE AGAIN. ONE MORE THING IS THAT I HAVE A BACKUP HERE AT MY OFFICE SYSTEM IT ALSO HAS DONE THE SAMETHING. I MADE A NEW DATABASE AND TRYED TO IMPORT DATA WITH DISTINCT KEYWORD BUUT IT IS NOT COPYING IT AS WELL. URGENT RESPONSE REQUIRED IF ANYONE CAN DO.It's that damn Miracle thing againnn....I hate when that happens...

Who else has sa rights to the server?|||Computers are like dogs. They can sense fear. You need to stop panicking and find out exactly when this occured, what was running, who was logged in, what the last code changes implemented were, etc...
Standard problem solving mode.

SQL Server will not, of its own accord, drop keys and duplicate records. Someone or some code did this.|||...and if I could write queries and triggers for future problems I'd be a wealthy man.|||Originally posted by blindman
...and if I could write queries and triggers for future problems I'd be a wealthy man.

LOL

Still working on the AI_DBA module?|||Oh, I finished that months ago. Works like a charm. But if I release it, I'd be out of a job.

If someone invented a light bulb that never burned out, who would want to manufacture them?|||What does that module do?|||AI_DBA module automatically and flawlessly performs all Database Administration and Development duties without the need for high-priced talent. It's user inteface consists of a single button that says "OK". It senses the manner and duration of the keypress to intuitively analyze the user's intentions and create a complete Requirements Document based upon the user's subconcious needs rather than just what they SAY they want, and completes the task within and regardless of all the unreasonable restrictions placed upon it by a boss who "knows better". It's "I told you so" submodule has been completely removed to spare any egos, and if you have an internet connection it automatically logs into dbforums three times each day and answers all questions containing the text words "date" and "format".|||OK-OK, I got the drift :)|||Originally posted by blindman
AI_DBA module automatically and flawlessly performs all Database Administration and Development duties without the need for high-priced talent. It's user inteface consists of a single button that says "OK". It senses the manner and duration of the keypress to intuitively analyze the user's intentions and create a complete Requirements Document based upon the user's subconcious needs rather than just what they SAY they want, and completes the task within and regardless of all the unreasonable restrictions placed upon it by a boss who "knows better". It's "I told you so" submodule has been completely removed to spare any egos, and if you have an internet connection it automatically logs into dbforums three times each day and answers all questions containing the text words "date" and "format".

Yup...that's about right...

I like the subconcious part...