To understand how recovery staging works with copy-only or cloaked backups, 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
A persistent frozen image backup is created several times per day and expires when the next one is created. This backup is created as copy-only (SQL Server 2005 or later) or it is cloaked (SQL Server 2000).
Table: Sample backup schedule using cloaked backups shows an excerpt from this schedule.
Table: Sample backup schedule using cloaked backups
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 persistent frozen image backups are cloaked. However, the differential backups would each be cumulative with respect to the last full backup that is non-cloaked.
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 cloaked 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.
If you use copy-only or cloaked backups, then the copy-only or cloaking 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.