Does only a full backup (and no tlog backup) of a db in full recovery model allow for point in time recovery?



  • 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?



  • No, it's not possible. You can find that info on the https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15#arguments 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 https://www.sqlshack.com/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 https://dba.stackexchange.com/questions/252191/during-a-log-backup-is-the-data-backed-up-to-the-start-or-end-of-the-operation/252242#252242




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2