Fuzzy backups and RESTORE DATABASE WITH STOPAT

So what’s a “fuzzy backup” and how does it relate to STOPAT?

 

There has been some confusion about what RESTORE DATABASE WITH STOPAT does, if it works, and why it behaves as it does. This is not about RESTORE LOG WITH STOPAT, but only RESTORE DATABASE. To understand how this works and why, you need to understand the concept of fuzzy backups and how they work at restore time.

 

When we restore a database backup, we need to end up with all pages in the database at the same effective LSN. This is a challenge when you consider that it may take hours to copy all the pages in a very large database.

When a database backup starts, we note the LSN which is the min of:

· The last checkpoint

· Repl-start

· The oldest active transaction.

.
We then start copying pages. Some are copied at the beginning of the
backup.
Some may be copied hours later, depending on the size of the database.
In order to restore a database which is self-consistent, when we're done all
the pages need to be at the same effective LSN.
We could do that by locking the database during the backup, but that MIGHT
cause some of you operational problems :)

What we do instead is note the LSN when the last data copy operation
completes, and copy the portion of the log from the begin backup to the end
backup LSNs into the backup file.
The last stage of a database restore is to use that segment of log to run a
REDO pass to bring all pages up to the state as of the end of the backup.

Getting back to STOPAT:

 

A full database backup can only be restored to the time of the end of the
backup. That means that you can’t STOPAT a point midway through the backup. Consider: Some pages were copied after that STOPAT time, so we cannot make the database consistent.

 

So what good is STOPAT when restoring a database? Its purpose is to flag an error if the backup is too recent to be able to reach the STOPAT time. For example:

· Full backup starts at T1

· User decides to restore database to T0, using a combination of full and log backups.

· If the backup in step 1 is used, the database cannot be restored to T0, so backup throws an error.