Newbie: Copy database but not tables

Question

Newbie: Copy database but not tables

Lionel2007

Old Hand

Points: 366
More actions
October 1, 2007 at 7:45 am

#153868

Hi,
I am using SQL 2000. I have two DB's one is live and the other is develpement.
I need to copy the live DB over the developement DB but I can't overide certain tables.
How do I go about? Do I need to this using transact?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

because you don't want to overwrite the database, it's much more difficult. foreign keys for tables may bite you in the tailfeathers.

this is what I would do:

restore a copy of Production as a 3rd database for development.

rename the tables you didn't want to loose in this 3rd database to _BAK

Do a SELECT * INTO 3RDDATABASE.dbo.TABLENAME From DEV.dbo.TABLENAME

for each table you were trying to get. hopefully, the foreign keys won't bite you.

If that works , use the 3rd database, or rename DEV to DEV_BAK and rename 3RDDATABASE to DEV (sp_renamedb)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bledu SSChampion Points: 12335 More actions · Answer 2

first find out why you can't override the tables. what error message are you getting etc.

to copy your tables you can either write your own sql or use of the shelf tools. my favoured one is redgate sql compare and they have data compare as well. but as i said you can still use the sql tools to do the job

Everything you can imagine is real.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

My guess is the errormessage in this case would be 'screaming developers' or similar 😀

Lionel2007 Old Hand Points: 366 More actions · Answer 4

There is no error message. I am trying to find a way to restore a database without overwriting some tables. The reason is our users entered test data in some of the tables to test the reports they do not want to enter the data again.

Vladan SSC-Insane Points: 22072 More actions · Answer 5

Ahhh... a test environment and lazy users. Been there, too...

If you want to do it, maybe you could use something in the way Lowell posted - transferring just the few tables that were affected, or some rows from them. But I wouldn't do that in your place. Allow me to present a few possible problems:

1) The application that was used to enter the data can be inserting or modifying rows in other tables than just those where the users think they have entered something. You could end up with orphaned rows or missing information that would be hard or impossible to fix after the database has been in use for some time. Even if you have precise documentation, it will mean you have to check everything carefully and be sure you don't forget anything.

2) When users know that they are in test environment, they don't pay such attention to doing everything correctly. After all, it is just a test, isn't it? Are you sure no-one has entered something wrong?

3) Are you sure you want to bring all rows from certain tables to production (or that you can easily and precisely specify which should be kept and which not)? Users like to have fun when testing, and they often enter things like "Tantric Sex" into product table, phoney names and addresses and so on. Recently, one Czech news portal tested their new feature, sending SMS to subscribers (generally, these were newspapers, press agencies etc.). Some bright head entered as a test message "Paroubek has gone mad" (Paroubek is the boss of our SocDem Party). It was supposed to generate but not actually send the messages - but alas, there was some error in the code and the message was sent to all subscribers :w00t:

So, my advice would be to never transfer anything from test to production, because the risk is too high... or at least try all possible ways to avoid it and only do it if there is no other choice. So far, I have been successful with it, and users know that if they test something, they will then have to enter it again into production DB. It helps if you explain that to them in advance.

Good luck!