Clicky

For some reasons, i lost a customer record which includes his details and transaction history.

Now, i found it from Backup database and i need to push it into live or production database.

His records are affected around 5 Tables.

How should i push it into Live or Production Database ?

asked 12/16/2011 12:17

chokka's gravatar image

chokka ♦♦


13 Answers:
I would do it as a scirpt.

Bulid the scirpt from the back up database.

that will insert the data into the proper tables. (using inserts and updates as needed)

you will need to make sure that the order is correct if you have any constraints on the fields
link

answered

Emes's gravatar image

Emes

My preferred way to do it is to create a new database from the backup on the same server and instance, and then manually construct the necessary statements, like

insert into production.dbo.table1 select * from backup.dbo.table1 where customerID = 1234;

That gives you much better control over the data you import, and you can manipulate, verify, make sure no duplicates occur, aso.
link

answered 2011-12-16 at 08:21:33

Qlemo's gravatar image

Qlemo

Customer Table has Identity Column

It lost the customerid = 1234

How to push it into identity column ;

Like do you want me to keep ..

set identity_insert CustomerTable ON  

???
link

answered 2011-12-16 at 08:27:03

chokka's gravatar image

chokka

Yes, you will need that setting for restoring the data. It should only be IDENTITY on that single table, but other tables might have own IDENTITY columns.
link

answered 2011-12-16 at 08:29:36

Qlemo's gravatar image

Qlemo

What about Date ?

'2011-10-26','2011-11-01'      


For example, date reflect as ..


How to insert the Date Field ?
link

answered 2011-12-16 at 08:33:06

chokka's gravatar image

chokka

Like , How to keep the date for example ...
link

answered 2011-12-16 at 08:54:55

chokka's gravatar image

chokka

I don't get you. You do not need to change anything, do you? So just transfering the values as-is should be fine. You have only issues with values automatically generated, like rowversion/timestamp, identity, GUID and the like.
link

answered 2011-12-16 at 08:55:12

Qlemo's gravatar image

Qlemo

Msg 547, Level 16, State 0, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Members_CaseManagers'. The conflict occurred in database 'MedIS', table 'CaseManagers', column 'CaseManagerID'.
The statement has been terminated.

(0 row(s) affected)
link

answered 2011-12-16 at 09:00:24

chokka's gravatar image

chokka

Insert statement is conflicting, What could be the reason ?
link

answered 2011-12-16 at 09:10:06

chokka's gravatar image

chokka

Since it is foreign key, you should check that one. Probably the CaseManagerID was not found, and needs to be inserted first into the CaseManagers table.
link

answered 2011-12-16 at 09:10:24

Qlemo's gravatar image

Qlemo

the case manager Id is not defined in the foreign table.

Are you sure you only lost that one set of data and not more?

You may need to check you back up table and make sure the data is lining up correctly

This is why I do it in a script.  I test it in a copy of production so I know it is ok.

then back up prod and run it on production.

 
link

answered 2011-12-16 at 09:16:32

Emes's gravatar image

Emes

you also may need  to worry about any triggers that you have defined on the affected tables...

as emes says you need to script this restoration process, and rigorously test it before attempting it in production....

His records are affected around 5 Tables.

this sounds like you haven't confirmed the scope of the problem yet...
and as emes suggests are you absolutely certain its just this customer...
better to spend some time confirming the scope of the problem rather than possibly componding problems by attempting ad-hoc batch of restoration....

it maybe prudent to actually take your database offline until the cause of the problem is determined.... could you restore the whole database to a point in time before the customer is lost?

link

answered 2011-12-16 at 09:21:45

Lowfatspread's gravatar image

Lowfatspread

Thanks
link

answered 2011-12-16 at 10:30:01

chokka's gravatar image

chokka

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

Asked: 12/16/2011 12:17

Seen: 274 times

Last updated: 12/16/2011 03:26