Sample backup schedule using copy-only backups
To understand how recovery staging works with copy-only, consider a sample backup schedule with the following characteristics:
The transaction log is backed up frequently, e.g., every two hours
A full backup is saved to secondary tape storage once every several days
Differential database backups are created several times per day
An Instant Recovery backup is created several times per day and expires when the next one is created. This backup is created as copy-only.
Table: Sample backup schedule using copy-only backups shows an excerpt from this schedule.
Table: Sample backup schedule using copy-only backups
Time | A full backup saved to secondary storage | Differential backup | PFI Copy-Only | Transaction log backup |
|---|---|---|---|---|
Day 1 | ||||
12:00 A.M. | X | X | ||
2:00 A.M. | X | |||
4:00 A.M. | X | X | ||
6:00 A.M. | X | X | ||
8:00 A.M. | X | |||
10:00 A.M. | X | X | ||
12:00 P.M. | X | X | ||
2:00 P.M. | X | |||
4:00 P.M. | X | X | ||
6:00 P.M. | X | X | ||
8:00 P.M. | X | |||
10:00 P.M. | X | X | ||
Day 2 | ||||
12:00 A.M. | X | X | ||
2:00 A.M. | X |
Under this schedule, full backups are performed every six hours. If a failure occurs, and is detected immediately, then you can restore the last full backup. Then you can replay, on average, three hours of transaction logs to achieve recovery. However, if a failure is not detected until after the next full backup, then there are not any full backups available. There are none available since 12:00 A.M. on day 1. The Instant Recovery backups are copy-only. However, the differential backups would each be cumulative with respect to the last full backup that is not copy-only.
In this example, suppose that an error occurs at 11:30 P.M. on day 1. But the error is not detected until 12:30 A.M. on day 2, after the 12:00 A.M. full backup. Since the 6:00 P.M. full backup no longer exists it would be necessary to begin the recovery with the backup taken at 12:00 A.M. on day 1. However, since all of the full backups were copy-only since then, the differential backup from 10:00 P.M. would be cumulative with respect to that backup. The recovery sequence would be restore the 12:00 A.M. day 1 backup. Restore the 10:00 P.M. differential backup. Restore the 1½ hours of transaction log backups.
The copy-only attribute appears in the properties for the snapshot backup image. Differential backups are automatically associated with the correct full backup. The SQL Agent recognizes these backups when it selects the recovery set for the full database restore.