October 1, 2007 at 7:45 am
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?
October 1, 2007 at 8:21 am
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
October 1, 2007 at 8:23 am
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
October 1, 2007 at 8:42 am
My guess is the errormessage in this case would be 'screaming developers' or similar 😀
October 1, 2007 at 8:48 am
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.
October 1, 2007 at 12:20 pm
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!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply