Have full SQL Server restore but losing 1 day of data

Posted on

Question :

I have SQL 2008 R2 64bit and it is backed up with ASIGRA SQL backup. The issue I have is that we had corruption on the server and the DB got corrupted yet the LDF file is in tact and does have all the changes since the last backup.

So I can easily restore the backup, but Can I then implement the changes logged in the LDF file so I can get back the day of changes we lost.

Answer :

First things first, before you restore anything – back up your log with no_truncate.

If your database is offline, then take a copy of your LDF file first, then bring your database online, ignoring any errors, and then do your log backup.

This log backup is then the last one in your recovery process, which involves a full database backup which doesn’t have corruption, and all the log backups since.

But also, consider what kind of corruption it is. If it’s in a non-clustered index only, then you could drop and recreate the index and remove your corruption that way.

You start by making a tail of the log backup of the damaged database, backup log database to disk = 'disk' with no_truncate.

You then restore the database from your last full backup backup, with no recovery and then restore your all your log backups ending with the one you made with no_truncate see this kb article: https://technet.microsoft.com/en-us/library/ms189606%28v=sql.105%29.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *