0

I have a database in full recovery model.

Full backups are configured to run every Sunday and log backups run every day.

Log backup files are destroyed after 30 days. Full backups are destroyed after 6 months.

I understand that to restore to a point in time within the last 30 days, I can apply the latest full backup from the Sunday before the point in time recovery required point, followed by applying the log backups and specify a time to restore up to.

However, suppose I have to restore to point in time on the 40th day. There are no log backups as they are deleted after 30 days. I only have the weekly full backups. Is point in time recovery possible using only the full backup from the Sunday immediately following the 40th day?

Improve this question
3
  • 1
    You mentioned no log backups but wouldn't you still have the log backups after the last Sunday full backup because those are less than 30 days old? The log backups before the last full are unneeded in this scenario. The latest full backup, the daily log backups since, and a final log tail backup before the restore will allow recovery without data loss. and PIT recovery.
    – Dan Guzman
    Jan 15, 2022 at 11:55
  • Ronaldo answers your question well. A simple way to think about it while you're still getting used to backups: you can only recover to a point in time of any backup file you have that was taken as of that time. If you only have a full backup as of a Sunday & no newer backup files since that full backup, then Sunday is your latest point in time. One caveat being that if the backup you restore is not a full backup, then you also need the nearest full backup before that file, and all of the backup files between the full and that file which are part of the same backup chain, to be able to restore.
    – J.D.
    Jan 15, 2022 at 13:20
  • @dan - sorry I meant 40th day in the sense 40 days ago.
    – variable
    Jan 15, 2022 at 13:25

2 Answers 2

Reset to default
4

No, it's not possible. You can find that info on the BACKUP doc:

When you restore a backup created by BACKUP DATABASE (a data backup), the entire backup is restored. Only a log backup can be restored to a specific time or transaction within the backup.

This limitation will be clear by Understanding SQL Server Backup Types:

A full backup, as the name implies, backs up everything. [...] This is a complete copy, which stores all the objects of the database: Tables, procedures, functions, views, indexes etc. Having a full backup, you will be able to easily restore a database in exactly the same form as it was at the time of the backup.
[...]

The log backup, as its name implies, backs up the transaction logs. [...] A transaction log file stores a series of the logs that provide the history of every modification of data, in a database.

As you can see, the full backup is just a copy of the whole database as it was at that moment whereas a log backup contains each transaction (individually logged like a step by step recipe) that happened since the last log backup.

The fact that you took log backups doesn't "upgrade" a full backup to be capable of doing a point in time recovery by itself if the log backup files were discarded afterwards. The log backups are still needed to perform a point in time recovery of the 40th day as you described.

Therefore, if you wanna be able to restore the 40th day using point in time recovery from now on, you should change your backup plan to keep enough log backups to cover that period.

To have a better understanding of the content of a log backup see During a log backup is the data backed up to the start or end of the operation?

Improve this answer
1

As already has been answered: No, you can't do point-in-time restore for anything but log backups. I'd just like to add a couple of minor details to the answer and comments already provided:

There is a STOPAT option for the RESTORE DATABASE command. But that doesn't allow you to stop at some time (like it does for the RESTORE LOG command). It is only there so MSSQL can give you an "early error" in can you have the intention to stop at some time prior to this full backup. I.e., imagine below

  1. 14:00 log backup
  2. 15:00 full backup
  3. 16:00 log backup

And now imagine you try to restore 2 and specify STOPAT 13:00. No can do, you need to start from an earlier full backup for that. By specifying that you have the intention to stop at 13:00, SQL Server can now give you an error when you do restore 2 above so you don't waste time restoring this full backup, a bunch of log backups and then see that error when you restore that last log backup.

Another detail is that the state of the database when you restore a full (or differential) backup is from the end of the backup command, not the start. I.e., start a backup 02:00 and it finishes at 02:15. Restore this backup and the state of the restored database will be from approx 02:15.

Improve this answer

Your Answer

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

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