Restore records
-
Hi
I have a problem with deleting records from DB.
The story goes like...
Some couple of thousands records has been deleted from the database a week back(7 days), Now they noticed that they need those records again in the DB, ofcourse they do have all backups. Since its been a week while.. they are many records updated then after. How can I append those records to the current database. I just need to add only those deleted records and shud not lose the records which are updated then after.
Can any one tell me how can I effeciantly deal with this scenario.
-
You'll have to restore the backup from a week ago to another database. Find the records that were deleted and insert them into the table in the original database.
Hope that helps,
-
to expand on what SQLZ said, with an ugly example:
do this on test copies until you are sure:
--identify the missing records, change the tablenames and dbnames to what is appropriate
SELECT * FROM RESTOREDDB.DBO.TABLENAME
LEFT OUTER JOIN PRODDB.DBO.TABLENAME ON RESTOREDDB.DBO.TABLENAME.PRIMARYKEY=PRODDB.DBO.TABLENAME.PRIMARYKEY
--EXISTS IN DB1, BUT NOT IN DB2
WHERE PRODDB.DBO.TABLENAME.PRIMARYKEY IS NULL
--finally, insert those records
SET IDENTITY_INSERT PRODDB.DBO.TABLENAME ON
INSERT INTO PRODDB.DBO.TABLENAME
SELECT * FROM RESTOREDDB.DBO.TABLENAME
LEFT OUTER JOIN PRODDB.DBO.TABLENAME ON RESTOREDDB.DBO.TABLENAME.PRIMARYKEY=PRODDB.DBO.TABLENAME.PRIMARYKEY
--EXISTS IN DB1, BUT NOT IN DB2
WHERE PRODDB.DBO.TABLENAME.PRIMARYKEY IS NULL
SET IDDENTITY_INSERT PRODDB.DBO.TABLENAME OFF
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! -
I used the following query
restore filelistonly
from disk = 'E:\DOTbackup6-7-06\dot_db_200606072012.BAK'
restore database TESTdot
from disk = 'E:\DOTbackup6-7-06\dot_db_200606072012.BAK'
with
move 'dotData' to 'E:\Data\testdot.mdf',
move 'dot_ext_data' to 'E:\Data\testdot_ext_data.ndf',
move 'hs_data_ext' to 'E:\Data\tesths_data_ext.ndf',
move 'dot_log' to 'E\data\testdot_log.ldf',
move 'dot_ext_log' to 'E:\Data\testdot_ext_log.ldf',
move 'dot_log_ext2' to 'E:\Data\testdot_log_ext2.ldf'
but getting this error.
Server: Msg 5105, Level 16, State 2, Line 7
Device activation error. The physical file name 'E\data\testdot_log.ldf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 7
File 'dot_log' cannot be restored to 'E\data\testdot_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.
-
Is your collation case sensitive?
It seems like is the only file with lowercase 'data' instead of 'Data'.
* Noel -
one thing to mention, the server where am trying to restore already has that Db but I am trying to resotore DB on diffrent name.
-
its not CASE SENSITIVE
-
You are missing the : on the path 'E:\data\testdot_log.ldf'
* Noel -
wowwwww. thanks for pointing out.
thanks a lot.
-
you are welcome
* Noel
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply