5

I'm pretty sure I know the answer to this, but the limitations of a point in time recovery blows my mind.

Say I have a 2TB database set to FULL recovery mode. I diligently take weekly full backups, daily diff backups, and log backups every 30 minutes.

Now, I want to roll the database back to 1 hour ago. Do I really have to restore the entire full backup, diffs, and logs up to one hour ago? Am I correct that this will take a very long time?

Is there no way to ROLLBACK the changes using the transaction log files so that this type of recovery takes a few minutes?

0

5 Answers 5

9

Yes, you would have to restore the full backup, at maximum one diff backup, and all necessary log backups to perform a point-in-time recovery in SQL Server. In SQL Server, you cannot roll back already committed transactions to go back in time. You would likely want to use the STOPAT syntax to restore your database to the exact desired point in time.

In fact, because you are doing transaction log backups every 30 minutes, SQL Server is also clearing out the transaction log with each log backup--so to go back to an hour prior, SQL Server wouldn't even have the necessary information to roll back that far, even if SQL Server had such a feature (which it doesn't, sadly).

I can't answer whether it would take a "very long time" because restore time varies quite a bit based on how large the database is, how many changes are contained in the log / diff backups, and how many log backups need to be restored. For a small database that isn't very busy, the restore could be quite fast, but for a very busy multi-terabyte database, it could take a while.

Oracle has a feature called Flashback that will let you do what you describe, but SQL Server doesn't have an equivalent feature.

3
  • Alright. Thanks for confirming. I guess I just couldn't believe this was true. But I couldn't find anything contrary to it either. Dec 18, 2019 at 20:10
  • Part of the reason SQL Server does not have this feature is that while the transaction logs contain enough information to rollback almost all kinds of change, there are some exceptions. Notably, when a page is deallocated, the transaction log does not store the old contents, just the fact that it was deallocated. so once the transaction completes and the deallocation actually takes effect, rollback is not possible. Dec 19, 2019 at 18:23
  • (Deallocated pages cannot be assumed to be unchanged since last allocated. Backups do not include deallocated pages, so restoring a backup could mean different contents in those pages vs the state when the backup was taken.) This is normally good because it means when dropping a table/index, it does not need the copy all the old contents of the table into the transaction log! But it has the side effect of making running the log backwards not always possible, and Microsoft probably don't want to spend the time to code this to only work in some scenarios. Dec 19, 2019 at 18:24
6

You could look into using the SQL Server snapshot feature depending on your use case. I use the feature prior to running production deployments and in non-Prod environments when I want to reset a DB to match specific test starting conditions. Since it utilizes sparse files, only copies modified pages to maintain the snapshot and allows you to maintain 1+ snapshots for specific points in time, they can be very effective, especially when tracking minute changes in a VLDB setting. One thing to be vigilant about is the amount of pages being modified as they will increase sparse file sizes accordingly and an outage may occur if you run out of space where the sparse files are situated.

2

There are a couple of good answers 1 & 2 about why you can't just rollback. In the answer by @AMtwo there is some talk about the speed of recovery. I wanted to expand on that a bit and address the question in your title.

Does a SQL server point in time recovery take as long as a full recovery?

  • Because you have to restore the full backup, and at least one t-log for a point in time (PIT) recovery, a PIT will always take at least as long as a full recovery.

  • Differential backups are generally taken to save room where you store your backups. If there is not a lot of change in your system, these are normally much smaller than a full backup. A single full backup, with 6 differentials and a weeks worth of t-logs normally takes significantly less space then 7 full backups plus t-logs.

A differential backup captures only the data that has changed since that full backup. Source

  • A differential absolutely requires a full backup, to be restored first.

  • If you wanted to, you could skip the differential, restore the full backup and all the t-log backups until your PIT with stopat

  • I do regular backup testing, and one of the big hurdles is the time to move the old backup from it is safe location (tape, off site storage, etc) to the server. This varies by your design. Only your business can know what this time is.

  • Once the backups are on the server they will be restored to; Ultimately the recovery time difference between full and PIT varies based on how much change there has been.

  • In my experience with 100's of PIT restores, the extra time for PIT over a full is minor, a couple of seconds or a minute. If you put the Full, the Differential and all the t-logs in a single folder, and use the GUI to for the restore (SQL 2012+), select all, and it automatically sort them, and restore to your PIT, without much difficulty or added time.

TL:DR Other then some variables that are specific to your business and design. a server point in time recovery takes about the same amount of time as full recovery.

2

Yes in short.

You will need to restore the last full backup, the latest differential backup and then all the log backups since the last differential backup.

This is the reason when a database grows too big, we usually ask the Dev team to purge the old data (older than retention period). Sometimes you do not have an option of purging and have to deal with it.

To be better prepared, always do an excercise of recovering the database in your Test environment, this will give you a rough estimate. Further to the estimate, an RTO can be agreed.

0

There's no guarantee that previous transactions are still going to be in the active transaction log, especially if you have backups every 30 minutes. Unfortunately you'll have to restore the latest full, followed by the latest differential, and then all of the transaction log backups taken since that differential.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.