Every DBA took at least once full database backup. But do you happen to know to what point in time this backup would be restored? To the moment when backup started? To the time when it finished? Or maybe there is a third option?
If you had chosen one of the first two answers, you really should read this post.
How full backup works?
A full database backup contains (we are going to look into this topic much deeper in the upcoming part of the series):
1. All allocated extents (extents that have at least one allocated page),
2. A part of transaction log that is necessary to recover the database from this backup.
To see this, let me create a sample database with a dummy table and then backup it:
BTW, by taking a full backup I also switched this database from auto-truncate mode —SQL Server is smart enough to figure out that until a full backup has not been taken, recovery of log backup is not possible, so log would be truncated during checkpoints. This is true regardless of the used recovery model.
Note. Log truncation (aka log clearing) is a misnomer, because during this operation nothing gets truncated or cleared. When a database is in auto-truncate mode all what happened during checkpoints is that zero or more VLFs is getting marked as reusable.
Back to full database backup. As we saw, the operation is divided into two parts: copying data pages is followed by copying required VLFs. Since all kinds of backups can be taken online, we can check to which point in time a database will be restored from a full backup by constantly inserting rows into our dummy table:
After this infinite loop has been running for about a minute, the log file has grown to over a hundred MBs:
This should be enough to demonstrate my point. While rows are still being inserted, let me perform another full database backup, but this time I will also record the times when it started and finished:
So far we know exactly when the full backup has started and ended. The second part of necessary information to answer our initial question has been recorded in a second column of the table (the column with default SYSDATETIME system function, a function that returns current time). Therefore, all we need to do is to recover our database from this backup and check the time at which the last row was inserted. In order to achieve this we should:
1. Switch to a different database (for whatever reason you cannot execute RESTORE command in a current database).
2. Force a test database to single user mode (and disconnect session inserting rows).
3. Restore it with REPLACE, because otherwise SQL Server will throw an 3159 error “The tail of the log for the database “BackupTest” has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.”
Selecting the time at which the last row was backed up is as simple as executing the following query:
As you can see, this time is different from both previously recorder. Therefore, a database is not restored from a full backup to the moment when backup started, neither to the time when it finished. The correct answer is a database is restored to the point in time when the data copy part of backup operation completes.
Can we change this time?
SQL Server allows us to specify the time to which a database should be restored — the STOPAT clause does exactly this. But can this clause be used with a full backup? Let me check:
As you can see, the time chosen to stop at was earlier than a data copy phase has finished. But this doesn’t matter really — I could as well take the point in time when a log was being copied. In both cases the restore will complete without any warnings. Nonetheless, if we check the time to which the database was restored, we will find something strange:
This is exactly the same time as previously. Apparently, the STOPAT clause was ignored by SQL Server. But if you choose a point in time later then a backup finished, the result will be quite different:
This time a database was left in restoring state, which is great in my opinion:
· A full backup is required to switch a database from auto-truncate mode.
· A full backup can be only restored to the point in time when a data copy phase finished.
· You can safely add STOPAT clause to RESTORE DATATABSE statement — it will be either ignored or used to postpone a recovery if needed.
Next time we will dig into a full backup contains.