MSPSS: is there life after the helpdesk?

sharing solutions to uncommon IT problems

Posts Tagged ‘t-logs

SQL Server: Point in time restore using transaction logs

leave a comment »


if you don’t have a DBA on call at all times it might be useful to keep at hand a simplified procedure on how to restore a database pointing to a specific point in time.

The first assertion we have to make is that: “point in time restore” is only possible if the database is set to “Full or Bulk recovery” mode (DB properties -> Options).

If the DB is set to simple, your only option is restore the last Full + Differential (if any) backup.

I suggest transaction log backups should be stored on a remote server (in case your SQL Server has a hardware failure).

The reasons why you might like to restore a database to a particular Point in time can be (but not limited to):

  1. A publication was made which compromised the integrity of data (therefore you will want to restore to 1 minute before the publication had been executed).
  2. Hardware failure (in which case, if the disk is compromised, you might not be able to backup the tail of the log thus, the latest version of the db will be the last t-log backup performed.

Transaction log restore procedure:

  1. Copy all backup files to the SQL Server: the latest Full backup and all t-log backups after that.
  2. Restore the latest Full backup (paying attention to select the NORECOVERY option). I usually restore the database with a different name (e.g.: dbname_temp) and rename it to the PROD name once I’m sure everything is ok)Image
  3. Right click on the database (which should be labelled as “Recovering…”) Tasks -> Restore -> Transaction logs
  4. Here comes the tricky part: with t-log restores, you are only allowed to select backups taken after the time and date of the last restore. If you like me, store all t-logs of a day in a single file, you’ll not be able to use the UI as it doesn’t allow to pick which ever t-log you want but it automatically selects all backups before the one you selected and you’ll end up with the following error:
    The log in this backup set terminates at LSN 9386000024284900001, which is too early to apply to the database. A more recent log backup that includes LSN 9417000002731000001 can be restored.
    What you can do is:

    • take note of the number of the first valid t-log backup
    • select all t-logs
    • click “script”

    this will produce a script with one transaction for each t-log backup, delete the ones taken before the full backup and execute (using the number you’ve noted earlier).

  5. If you have more t-log backups to restore (e.g.: the failure occurred the day after the Full Backup restore) remember to set again the NORECOVERY option
  6. When you have reached the t-log that contains the point in time to which your restore should stop, select all t-logs including the one that contains your “point in time” and then select the exact “point in time” at the bottom of your “Restore Transaction log” window
  7. If your “point in time” is too recent and it has not been backed up yet, make a transaction log backup of your production database (assuming you still have access), restore all previous t-logs and then restore the latest with option “point in time” set.
  8. Also remember to change the recovery option to RECOVERY when restoring the last t-log in order to make it “ready to use”



Written by zantoro

January 14, 2013 at 1:22 pm

Posted in SQL Server

Tagged with , , ,