MSSQL Rollback

Restoring a column in a table from a backup. IF you have accidentally deleted a column from a table or corrupted the data for that column and you cant restore from a backup due to new records being entered this is the best way to get the data back for at least 99% of the records.

What we will do here is restore the database to a temporary database then copy the relevent columns back into the original database.

1. First step is to restore the database from a backup. In this instance I restored to ‘dev’

2. Next run this command to verify the results


BEGIN TRANSaction
SELECT * FROM dev..tableToRecover;
UPDATE prod..tableToRecover
  SET fieldToRecover = (SELECT fieldToRecover FROM dev..tableToRecover
  WHERE prod..tableToRecover.code = dev..tableToRecover.code) ;
select * from prod..tableToRecover;
ROLLBACK

3. When you are happy with the results just replace the ROLLBACK command with COMMIT to force through the changes


BEGIN TRANSaction

SELECT * FROM dev..tableToRecover;

UPDATE prod..tableToRecover
  SET fieldToRecover = (SELECT fieldToRecover FROM dev..tableToRecover
  WHERE prod..tableToRecover.code = dev..tableToRecover.code) ;

select * from prod..tableToRecover;
COMMIT

Leave a Reply