There are chances of getting unintentional update or delete to a table, in that case restore the most recent backups is the best solution using which we can get the data almost completely.
Instead of restoring the backup to the original database, it is better and convenient to restore the backup files to a newly created database, since it is very easy to fetch only the data that has got deleted or updated to the original database
First step is to restore the last full backup to the database by using the following script,
RESTORE DATABASE testdb
FROM C:\backup\originaldb.bak
WITH MOVE original_data TO 'D:\data\test.mdf',
MOVE original_log TO 'D:\log\test.ldf',
NORECOVERY, STOPAT = '01/07/2012 12:10 am'
STOPAT option should be the approximate time at which the update or delete action was performed on the table
NORECOVERY option is used to make the database unavailable for the users to access. It specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence. It tells SQL Server that recovering the database is not finished and that subsequent restore files will occur.
Next step is to restore the transaction log files, starting with the first transaction log backup that was created after the full backup. Restore the log files in sequence with NORECOVERY option.
RESTORE LOG testdb
FROM C:\backups\originaldb_transationallog1.trn
WITH MOVE original_data TO 'D:\data\test.mdf',
MOVE original_log TO 'D:\log\test.ldf',
NORECOVERY, STOPAT = '01/07/2012 12:10 am'
RESTORE LOG testdb
FROM C:\backups\originaldb_transationallog2.trn
WITH MOVE original_data TO 'D:\data\test.mdf',
MOVE original_log TO 'D:\log\test.ldf',
NORECOVERY, STOPAT = '01/07/2012 12:10 am'
Finally restore the database with recovery option to make the database available to use. It signifies that roll back should be performed after roll forward is completed for the current backup.
RESTORE LOG testdb WITH recovery
Now to get the data from newly restored db to an accidental updated/deleted table in the original database, use the following query
UPDATE A
SET A.col=B.col
--specify the col which has got updated or deleted
FROM originaldb.dbo.Tbl_tht_needs_to_be_updated A
INNER JOIN testdb.dbo.tbl_newly_restored B
ON A.pkeycol=B.pkeycol
--Use where condition if you want to restore specific data
Note: If you have same database with the tables having same data in different server, you can create linked server to fetch the data and restore it to the updated/deleted table.
