Log Backups
-
Hi,
Let's suppose that we had a full database backup from Friday night. Thereafter, we had differential database backups for each night. We have log backups for each hour. The database crashes Thursday. We start by restoring the full database backup from Friday night. Then we restore the Wed night differential backup. Next, we start applying log backups to bring the database to the point of failure. Can SQL Server tell from what point in the first transaction log backup does it have to roll forward the changes, or does it roll forward all the changes in the transaction log backup? Also, identifying the first log backup to be applied is the DBA's job or does SQL Server help in this regard. I would highly appreciate if you clarified this.
Karim
-
Karim,
If you use TSQL scripts to perform the restore you definitely as DBA need to have knowledge about the correct log sequence and yes SQL will check regardless if it is the appropriate TLOg backup is being restore.
If you use EM UI you already have more "help" from it because it will visually indicate that you have selected an invalid Tlog sequence without actually executing any restore command.
No matter what you do the recovery options have to me specified manually on either case
Cheers,
* Noel -
What do you mean by the first transaction log?
You would restore your Friday Full backup, your Wednesday night Differential, and any Transaction Log backups made AFTER the Wednesday night Differential. Remember, the Differential backup has all the same changes on it as the Transaction Log backups made between the Full Backup and the Differential. If you restore a Differential backup, there is never a need to restore Transaction Logs backup made BEFORE the Differential.
You would have to figure out which Transaction Log backups that would be.
-SQLBill
-
The whole point of the diff backup is that after you restore the full backup, you do not have to apply each trans log still the point in time - which could take forever. Instead you restore the Full backup, followed by the diff and then apply all trans log backups since the diff backup. You also need to look and see if your current backup scenario is adequate. Remember, having a good backup does not only mean that you are in a position to restore all the data till the point of failure, but also how quickly you can restore the data and bring up the DB.
-
Not sure exactly what the last two posts refer to as the initial question does not imply that they want to apply tlog backups between diff backups. The method of applying full backup from Fri then diff backups to Wed night is fine. You then want to apply tlog backups to move forward in time prior to failure on Thurs (and presumably prior to the diff backup you ran on Thurs). Not sure what you are asking re tlog backups and SQL servers knowledge?? The tlog backups you create are hopefully timestamped in the filename (or the date modified flag as a worse case) so this should help (assuming you know approx what point in time the failure occurred). As the other post indicated EM can give you a GUI interface to tlog backup sequence to apply but again you still need to know at what point you want to stop. Either way the apply of tlog bakups is manual.
Derek
-
You could automate some type of restore for the logs based on timing, but I'd tend to do it manually. Be sure you are naming logs with some type of timestamp to make it easier.
As mentioned above, the order of restoring is correct: full,diff,logs. You would need to make sure that you did not specify the database as recovered in each step or you'd start over, so each restore line, or the radio box in EM, would need to specify NORECOVERY with more data to follow. Until the last one, that is.
SQL will reapply all transactions in the logs you restore. You could stop the last one early with a point in time recovery if you wanted, but not sure you'd want to do that here.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply