If I regularly backup my whole server where a SQL instance and database lives, and restore it to a different machine, can I use the database backups that have occurred (on the original server) after that full server snapshot was generated, to bring the newly restored server up to date?
Which kinds of database backups would I need available to be able to do so?
I'm guessing if I have a full backup of the database that is current, that would be sufficient, but if my last full backup was as old (or even older) than the server snapshot, could I use that full backup + any differential, transaction log, etc backups that came after that full backup to bring my server snapshot on the newly restored server back up to date?
Example (for simplicity let's assume each of these backups are completed instantly):
Server 1 has a full server point in time snapshot taken at 5:30 PM every day.
Database A on Server 1 undergoes nightly full backups at 11:30 PM every day.
Database A also has routine differential backups taken every hour throughout the entire day.
Database A also has transaction log backups taken every 15 minutes throughout the entire day.
Scenario 1:
Server 1 crashes at 11:50 PM.
Server 2 is restored from Server 1's full server point in time snapshot backup from 5:30 PM earlier that day.
Can we use the full backup (from Server 1) of Database A from 11:30 PM (20 minutes before the crash) to restore the database to as it was at 11:30 PM and then use the 11:45 PM transaction log backup to bring the database to as recent as possible (on Server 2)?
Scenario 2:
Server 1 crashes at 11:20 PM.
Server 2 is restored from Server 1's full server point in time snapshot backup from 5:30 PM earlier that day.
Can we use the full backup of Database A from the night before at 11:30 PM (since we didn't get to take one today yet before the crash) and then all the hourly differential backups between 11:30 PM the night before until 11 PM the night of and then the 11:15 transaction log backup to bring the database to as recent as possible (on Server 2)?
Is there a more efficient way of restoring in either scenario above and/or a better way processes on types of and frequency of database backups?
Best Answer
No. You cannot manually switch an ONLINE database to RESTORING and apply log backups, and you cannot attach a database and leave it in the RESTORING state.
This would be a useful feature, and it's been requested before. See eg here.
Any full backup, optionally a differential backup, and then the log backups from that point forward. Note that it doesn't have to be the latest full backup, as long as you have all the log backups going back to that full. Full and differential backups never break the log chain.