SQLTeam.com | Weblogs | Forums

How to restore deleted records in a production server

sql2008

#1

i have daily fullbackup at 12 am
differential backup at every 2 hrs,
transactional log backups every 1 hr.
some one deleted 100 records from my server ? ( server is online)

how to restore them?

can anyone help me?


#2

Restore TO A NEW TEMPORARY DATABASE from Full Backup, Differential backup (before the accident), each TLog backup after that, up to the accident, using STOPAT with a time shortly before the accident.

Compare the PKeys in Live and Temporary database for the damaged table to work out which IDs were lost.

Merge the missing records back into your table, e.g. something like:

INSERT INTO LiveDatabase.dbo.YourTable
SELECT *
FROM TempDatabase.dbo.YourTable
WHERE PKey IN (1111, 2222, ...)

if there are a large number of IDs then probably easier to put the into a #TEMP table and JOIN that instead of using "IN"


#3

As a bit of a sidebar, you're killing your disk space with all the DIF backups. If you're taking a full backup every 24 hours, just use a TRN log backup every hour. It doesn't take THAT long to restore 23 or 24 log files if it comes to that.


#4

I thought that too, but thought that recovery time might be important and rate of database change huge ... I never asked the question though!

Personally I think TLog backups hourly is useless ... if you need TLog backups, to minimise data loss in the event of a disaster, then who wants to lose an hours work when you could lose just a few minutes.

We backup our TLogs at 10 minute intervals but Tara posted recently that the big shops she talks to have moved to backing up ever minute or two ... I'm struggling to come up with a reason NOT to do that!!

That might then make one/some DIFF backups during the day important to reduce the number of TLog backups having to be restored after a disaster! Saves having to remember the syntax for the STOPAT Restore option though ... just restore to a one minute granularity based on the actual log backup files!!


#5

Sorry for the late reply.

Yep... I absolutely agree that 15 minute log file backups are better than hourly log file backups but, considering how many people I know we've both run into that have no backups at all, I'll take the hour rather than nothing. Also, just to be sure and I say with capital letters, IF you can take a tail-log backup when a disaster occurs, you can restore to a point in time where virtually nothing is lost.

Heh... of course, the funny thing is that I've seen people put their 15 minute backups on the same SAN as the data. Lotta help that does if the San is in a fire or a flood. :scream:


#7

you can detect those record by using undocumented function fn_dblog().

 SELECT [RowLog Contents 0] 
    FROM   sys.fn_dblog(NULL, NULL) 
    WHERE  
           AllocUnitName = 'dbo.TableName'        
       AND Context IN ( 'LCX_MARK_AS_GHOST', 'LCX_HEAP' )        
       AND Operation in ( 'LOP_DELETE_ROWS' )   
    ;

but this function cannot be helpful in restoring process, see here for more detail about this function may this help you http://www.sqlserverlogexplorer.com/reading-sql-server-transaction-logs/


#8

Just in case helpful this is what we use to mirror-copy the backup files to A.N.Other server (offsite obviously better than on-site, but on-site elsewhere is a 2nd-best choice and better than no-mirror IMHO)

ROBOCOPY s:\BackupFileSourcePath \\RemoteServer\RemoteShare\Remotepath *.BAK 
    /MIR /MT:8 /R:4 /W:10 /TEE /LOG+:c:\LogFilePath\RoboMirror.LOG 
    >>c:\LogFilePath\RoboMirror.ERR

That then needs scheduling regularly (as often as the most frequent backup interval), and then something else needs to check that it is actually working, i.e.

\\RemoteServer\RemoteShare\Remotepath

contains a file that is "recent", although a test that it contains ALL files would also guard against largest files exceeding disk space

(That doesn't have to be a share, it could be a mapped drive, but Mapped Drive is probably more "fragile")

And then something, else, needs to cycle/purge the Error and Log files (which are created Belt & Braces just in case they need reviewing if/when something bad happens ...

But maybe there are better / smarter ways of doing the same?


#9

Everything depends on when your data were deleted. you need to restore your database till the moment your records were deleted.

Anyway, you need to restore your full backup first of all. Then if you know the time when your records were deleted you can restore a differential backup and transaction log backups. For example:

RESTORE DATABASE your_database FROM DISK = 'full_00_00.bak' WITH NORECOVERY, REPLACE RESTORE DATABASE your_database FROM DISK = 'diff_12_00.bak' WITH NORECOVERY RESTORE LOG your_database FROM DISK = 'log_13_00.bak' WITH NORECOVERY RESTORE LOG your_database FROM DISK = 'log_14_00.bak' WITH STOPAT = '2015-11-19 13:29:59.000', RECOVERY

I'd recommend you to read this article https://sqlbak.com/academy/point-in-time-recovery it should help you to solve this case