How to recover data using a transaction log backup



By:

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
        

Last Update: 3/25/2014




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools