Overview
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.
Explanation
Example Restore Scenario
Let’s say you create a full backup every night at 12:00 am and transaction log backups every hour. Let’s now say that the server crashes at 3:30 am, and you get an alert, and the only way to bring this server back is to restore the data. If you restore the 12:00 am full backup, then the transaction log backups in order from 1:00 am, 2:00 am, and 3:00 am, you will have up-to-date data as of 3:00 am, only losing 30 minutes of data.
Things to Know
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 the importance of data loss, the size of the database, the number of transactions, and the 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 when:
- 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.
Sample Restore Script
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
Brady has been in the IT industry for 10+ years. He has worked in administrative roles using MSSQL 2000 to 2012 as well as Sharepoint 2007 and 2010. He currently serves as a Database Administrator in Nashville, TN. You can view his blog @ http://www.sqlbrady.com.
- MSSQLTips Awards: Trendsetter (25+ tips) – 2013
