Freigeben über

Fuzzy backups and RESTORE DATABASE WITH STOPAT

  • Artikel

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.

Comments

  • Anonymous
    October 26, 2007
    PingBack from http://cars.quemp.com/?p=7938

  • Anonymous
    May 16, 2013
    Please explain with example of scenario, where RESTORE DATABASE .... WITH STOP AT can be used in FULL and DIFFERENTIAL BACKUP.

  • Anonymous
    September 15, 2014
    I'm confused here. Maybe you can clear things up for me.  Are you talking about the use of the STOPAT command in RESTORE DATABASE statements or RESTORE LOG statements?  I've known that the STOPAT switch was ignored by SQL Server when passed in a RESTORE DATABASE statement, but that it actually allows for point in time recovery when passed with a RESTORE LOG statement.   Are you saying that it does not allow you specify an exact point in time to restore to when passed with a RESTORE LOG command?

  • Anonymous
    September 16, 2014
    I'm saying that RESTORE DATABASE WITH STOPAT can be helpful in two cases:

  1.  If you're going to be issuing a RESTORE DATABASE followed by a sting of RESTORE LOG commands, it would be nice to have all commands use the same WITH STOPAT clause.  Not functional in this case, but nice to have the symmetry.
  2. There is one case where the STOPAT clause on a RESTORE DATABASE command is not ignored:  If the time requested in the STOPAT clause is before the effective time of the backup, it will flag an error rather than overwriting your (potentially very large) database, and then discovering that the next log restore will fail because the DB is already past the STOPAT point. For example, if you had done a backup that started at 15:00:00 on 9/1/2014, with log backups happening every few minutes.  Say it was a very large database that took 2 hours to copy all of the data to the backup.  If you attempted to use that backup to restore a database using WITH STOPAT='9/1/2014 16:00:00', the restore would fail because the effective time of the backup would be around 9/1/2014 17:00:00. Without the STOPAT clause, the restore would succeed (taking a couple of hours to do so), but the log restore would fail because the database is already past the STOPAT point.