August 29, 2008 at 1:46 pm
I messed up and wiped out a field in one of our tables in our production database.
I do have a copy of that database that I can access, though, so my question is how do I read the value of that field in the copy and put it back in the live database?
I know that I need some kind of UPDATE ... SET statement, but I'm not sure how I incorporate two separate databases within it?
August 29, 2008 at 1:53 pm
Did you wipe out the value in that column for the whole table or just one row?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 29, 2008 at 2:05 pm
The whole table
August 29, 2008 at 2:15 pm
So, there are a couple of ways to do this but first of all, you need to be careful, especially being that you are dealing with production data.
Option 1 is to create a linked server and update the production table from the copy that you have.
Option 2 is to copy the table into the production database using the "import data" task in SQL Server Management Studio. Right click on the production database > Tasks > Import data. In this wizard you will have to point to the source server (this is the server that has a copy of the table that you have the good data in. Your destination server should auto populate as your production server as this is where you started the import. You will have to choose the table, then you will have to choose a destination table. PLEASE NAME IT DIFFERENT FROM THE PRODUCTION TABLE OR YOU WILL POTENTIALLY MESS THINGS UP WORSE. Finish the import wizard and let the data copy. When complete you will need to execute the update statement.
Please check this carefully so that you are not updating other data. You may want to export a copy of the production table as a backup somewhere else prior to running the update just in case. Sorry, I like backups.
Here is a sample update script that you should be able to use to get you going. Again, please check.
update ProdTable
set NowEmptyColumn = np.NotEmptyColumn
from
ProdTable pt join NotProdTable np
on pt.primary_id = np.primary_id --This is critical so, you need to know what to join on
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 29, 2008 at 2:25 pm
Assuming both databases are on the same server, but with different names, and the table has the same name in both copies, it will look something like this:
update table
set column = copy.column
from database1.dbo.table
inner join database2.dbo.table copy
on table.id = copy.id
In this, database1 is the one you want to update, database2 is the one to update from.
If the two databases are on separate servers, you'll need to add the second server as a linked server to the main server. Then you'll use that server's name in front of "database2". Like:
update table
set column = copy.column
from database.dbo.table
inner join server2.database.dbo.table copy
on table.id = copy.id
In this case, I'm assuming both copies of the database have the same name, so took the "1" and "2" off of that part, and added "server2" as the server with the good copy of the table.
If the table is at all large, you may want to move the databases to the same server and use the first solution above, because large updates across servers can be very, very slow.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2008 at 11:18 am
DavidB and GSquared:
Both my production database and its copy are on the same server, under the same instance of SQL Server, so the scripts you both provided for that purpose were what I needed.
I ran such a script and was able to fix my previous mistake, thanks for your help!
😎
September 2, 2008 at 11:22 am
Thanks for the update. I was wondering how things went for you and the follow up is appreciated.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 3, 2008 at 8:29 pm
From now on, when doing data manipulation on a production server, I would recommend placing the action queries in a transaction with a select statement checking the data. Only if the data looks good do you commit it!
IE:
BEGIN TRAN
UPDATE statement(s)
SELECT Changed Data
-- Rollback if not right
-- Commit if correct
This will save you hours of cleaning up after yourself!
Gary Johnson
Sr Database Engineer
September 4, 2008 at 6:57 am
Gary,
That is of course a very good point, and I will keep it in mind from now on...
Actually, what I meant to do at first, which would have avoided the problem I had in the first place, but I had another one of my too-frequent senior moments :blink:, was to try out my changes in a copy of the live database before working on the latter.
Duh....
Thanks for your input!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply