How to recover data using a transaction log backup

By: Brady Upton

When using the Full or Bulk Logged recovery models you can restore to a point in time depending on how often you create transaction log backups.

Let’s say you create a full backup every night at 12:00am and transaction log backups every hour. Let’s now say that the server crashes at 3:30am and you get an alert and the only way to bring this server back is to restore the data. If you restore the 12:00am full backup, then the transaction log backups in order from 1:00am, 2:00am, and 3:00am, you will have up to date data as of 3:00am only losing 30 minutes of data. Of course, you can set transaction log backups to occur every 5 minutes which makes your point in time of data loss only 5 minutes at the most. The optimal interval depends on factors such as importance of data loss, size of the database, number of transactions, and workload of the server.

Also, when you are restoring transaction logs, you have to restore WITH NORECOVERY until the last log. When restoring the last log, restore WITH RECOVERY

It’s not possible to restore a transaction log backup if:

• If a transaction log backup is damaged or missing, you can only restore up to the point of the damaged or missing log backup. Transaction log backups have to be in sequential order.
• You restore a database WITH RECOVERY before you are finished restoring all the transaction logs needed. If this were to happen, you would need to start over from the beginning with the Full backup.

The following example starts by restoring a full backup followed by two transaction log backups. Note using the NORECOVERY statement throughout except on the last restore.

        RESTORE DATABASE AdventureWorks2012
        FROM BackupDevice
        WITH NORECOVERY
        GO
        RESTORE LOG AdventureWorks2012
        FROM BackupDevice
        WITH FILE = 1, NORECOVERY
        GO
        RESTORE LOG AdventureWorks2012
        FROM BackupDevice
        WITH FILE = 2, RECOVERY
        GO