June 24, 2020 at 1:03 pm
I know that to restore a database and log backups, you need to not have any gaps in the chain. But does a missing full backup count as a gap?
A database server runs a full backup of the database every night. It further takes a log backup every 2 business hours (8AM to 6PM). To save disk space, we have a retention policy of only saving 73 hours of backups, with older backup files being sent to an off-site DR facility and deleted.
We recently discovered a bug with security that caused the deletions to fail, meaning we now have a few months of backups sitting in the folder. I was in the process of fixing the bug and had to delete a portion of full backups to avoid a critical disk usage issue, when the customer stated they wanted to perform a forensic examination of how the database looked during a period of dates.
For specifics, we have a full backup taken the night of 2/8, and one taken the night of 5/17, but we have all log backups in that window. Customer would like to restore the database on a test server to how it looked on 4/15, then 4/16, then 4/17, through 5/1.
Is it possible to restore the 2/8 full backup, then every log backup from 2/9 through 4/15 to restore the database, or would SQL Server see the missing 2/9 full backup as a gap in the chain? I know a full backup doesn't truncate the log, so I'm leaning to yes, but I've never had a crazy enough case to try it.
Second, if the customer then decides to view the 4/16 state, do I need to repeat the process over again starting from the 2/8 full backup, or is there a way I can resume my log backups to the restored 4/15 db?
June 24, 2020 at 2:03 pm
You are right in thinking that the full backup does not break the log chain - a full backup is a backup of the database, together with enough of the transaction log to be able to bring the database online in a consistent state.
So yes, to view the database as of 4/15 it would the full backup from 2/8 and then all the subsequent log backups.
Instead of restoring the last log backup with the RECOVERY option, if you used the WITH STANDBY option, then this will allow you to view the database in a read only state, and, more importantly for you, allow the subsequent restore of more log backups to then get to 4/16. And you can keep doing this with the log backups.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply