1

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?

Improve this question

2 Answers 2

Reset to default
3

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?

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.

Which kinds of database backups would I need available to be able to do so?

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.

Improve this answer
2
  • Just to clarify the distinction between your answer to my first question and my second question, you're saying the process to restore a database from backups in general requires a full backup and the transaction log backups since that full backup. But I wouldn't be able to do this to an online database, rather it would only be possible to do to a new database?
    – J.D.
    May 2, 2020 at 20:54
  • 1
    You can replace a database with RESTORE. And you can also drop and rename databases.
    – David Browne - Microsoft
    May 2, 2020 at 21:01
0

Snapshots are not backups. For protecting against crashes and for restoring purposes in case of a failure you need backups. The frequency of the backups depends on RPO,RTO,SLA-s.

Database snapshot is not a full copy of the database and it resides on the same server instance as the source database. You cannot restore from a snapshot if the database is corrupted or lost, nor on another server.

Snapshots are a read-only state of the database and should be used for reporting,auditing or maybe development purposes.

Have a look at the following documentation.

https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server?view=sql-server-ver15

Improve this answer
5
  • Sorry if my wording was confusing but I never mentioned database snapshots. When I said server snapshots, that's in regards to a server level backup and recovery tool and outside the scope of SQL. E.g. if I was using the built in Windows Server backup (learn.microsoft.com/en-us/windows-server-essentials/manage/…) or if my server is hosted in AWS and I used AWS Backup of the server.
    – J.D.
    May 2, 2020 at 15:43
  • So my question is, if I restore the whole server from a server backup (MS Server backup, AWS Backup, etc) to a new a server, but the database is outdated, can I bring the database up to date using full, differential, and transaction log backups that I also have even though I'm applying them to a database on a different server?
    – J.D.
    May 2, 2020 at 15:45
  • Yes, you can. The backup are all that mattes.
    – Tibor Karaszi
    May 3, 2020 at 6:25
  • @TiborKaraszi Per David Browne's answer above, it sounds like you can't with the original database itself but you can restore to a new database.
    – J.D.
    May 3, 2020 at 22:26
  • Restore creates a new database, and if the name exists it will be overwritten. There's a "safety check" that does various things in case you "by mistake" restore over the wrong database and you can override that safety check using the REPLACE option of the RESTORE command.
    – Tibor Karaszi
    May 13, 2020 at 14:02

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.