Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

Replace Record in Table from A Backup of the Same Database

I have a Live Database called LiveDB.
I restored a copy of a backup that was taken of LiveDB two nights ago. The restored database is called LiveDB_restore

Today, by accident, I overwrote one record on a table in the LiveDB. I want to replace that record from the data in LiveDB_restore.

The table name is Loans.
The primary key field in Table Loans is called RecID.
The RecID = "820d0d09"

I would like to accomplish this via a query in SQL Server Management Studio
Please help me.
0
nkraemer
Asked:
nkraemer
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
dqmqCommented:
Insert into livedb.SCHEMANAME.TABLENAME
   Select * From livedb_restore.SCHEMANAME.TABLENAME
where RecID = "820d0d09"

0
 
Aaron TomoskyTechnology ConsultantCommented:
Open a query window to livedb.
Insert into loans
Select * from livedb_restore.dbo.loans where
RecID = '820d0d09'
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Here you go:

INSERT INTO LiveDB.schemaname.Loans (col1, col2, col3)
SELECT col1, col2, col3
FROM LiveDB_restore.schemaname.Loans
where RecID = '820d0d09'

If it errors out, then you have that RecID already available and you need to update records using

UPDATE LiveDB_restore.schemaname.Loans
SET col1 = t2.col1,
col2 = t2.col2
FROM LiveDB.schemaname.Loans t1
JOIN LiveDB_restore.schemaname.Loans t2 on t1.RecID = t2.RecID
WHERE t2.RecID = '820d0d09'
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
nkraemerAuthor Commented:
rrjegan17
Having to match all 112 columns to each other will take forever. Is there a simpler way?
0
 
Aaron TomoskyTechnology ConsultantCommented:
My way should drop right in. Back it up first of course.
0
 
nkraemerAuthor Commented:
aarontomosky

when I try to INSERT I get error

Viloation of PRIMARY KEY constraint. Cannot insert duplicate key....
0
 
sshah254Commented:
Since you have updated the record (as opposed to deleting it), the only way it will work is you go with the update method that rrjegan17 suggested.

Since it is a PRIMARY KEY, DO NOT delete the record and try to insert it like others are recommending it.  You'll lose database integrity if you do that.

Ss
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Having to match all 112 columns to each other will take forever. Is there a simpler way?

Yes, Generate a INSERT script from SSMS which would have mapped all the 112 columns for you..
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now