Useful things about data recovery that every DBA should know. Part 1 — To what point in time full backup is being restored?

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:

CREATE DATABASE BackupTest;
GO

USE BackupTest;
GO
CREATE TABLE Tab
    (col1 int identity primary key,
    col2 time default SYSDATETIME(),
    col3 char(4100) DEFAULT 'a');
GO

BACKUP DATABASE BackupTest
TO DISK = 'InitailFullBackup.bak'
WITH INIT;
GO
--------
Processed 328 pages for database ''BackupTest'', file ''BackupTest'' on file 1.
Processed 6 pages for database ''BackupTest'', file ''BackupTest_log'' on file 1.
BACKUP DATABASE successfully processed 334 pages in 0.063 seconds (41.341 MB/sec).

 

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:

USE BackupTest;
GO
WHILE 1=1
    INSERT INTO tab DEFAULT VALUES;
GO

 

After this infinite loop has been running for about a minute, the log file has grown to over a hundred MBs:

DBCC SQLPERF(LOGSPACE);
-----
Database Name    Log Size (MB)    Log Space Used (%)    Status
BackupTest        111,7422        95,54202                0 
…

 

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:

PRINT 'Backup started at ' + CAST(SYSDATETIME() AS VARCHAR(50))
BACKUP DATABASE BackupTest
TO DISK = 'BackupTest.bak'
WITH INIT;
PRINT 'Backup ended at ' + CAST(SYSDATETIME() AS VARCHAR(50))
GO
-------
Backup started at 2013-07-14 07:47:10.4692339
Processed 18952 pages for database ''BackupTest'', file ''BackupTest'' on file 1.
Processed 239 pages for database ''BackupTest'', file ''BackupTest_log'' on file 1.
BACKUP DATABASE successfully processed 19191 pages in 0.368 seconds (407.416 MB/sec).
Backup ended at 2013-07-14 07:47:11.9392360

 

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.”

USE master;
GO

ALTER DATABASE BackupTest
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

RESTORE DATABASE BackupTest
FROM DISK ='BackupTest.bak'
WITH REPLACE, RECOVERY, STATS=10;
GO

 

Selecting the time at which the last row was backed up is as simple as executing the following query:

USE BackupTest;
GO
SELECT MAX(col2) FROM tab;
GO
------
(No column name)
07:47:11.5192354

 

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:

RESTORE DATABASE BackupTest
FROM DISK ='BackupTest.bak'
WITH REPLACE, RECOVERY, STATS=10,
STOPAT = '2013-07-14 07:47:11.00';
GO
------
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 18952 pages for database ''BackupTest'', file ''BackupTest'' on file 1.
Processed 239 pages for database ''BackupTest'', file ''BackupTest_log'' on file 1.
RESTORE DATABASE successfully processed 19191 pages in 1.622 seconds (92.434 MB/sec).

 

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:

USE BackupTest;
GO
SELECT MAX(col2) FROM tab;
GO
------
(No column name)
07:47:11.5192354

 

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:

USE master;
GO

RESTORE DATABASE BackupTest
FROM DISK ='BackupTest.bak'
WITH REPLACE, RECOVERY, STATS=10,
STOPAT = ''2013-07-14 07:50:11.00'';
GO
------
RESTORE DATABASE BackupTest
FROM DISK ='BackupTest.bak'
WITH REPLACE, RECOVERY, STATS=10,
STOPAT = '2013-07-14 07:50:11.00';
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 18952 pages for database ''BackupTest'', file ''BackupTest'' on file 1.
Processed 239 pages for database ''BackupTest'', file ''BackupTest_log'' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE DATABASE successfully processed 19191 pages in 1.516 seconds (98.897 MB/sec).

 

This time a database was left in restoring state, which is great in my opinion:

SELECT DATABASEPROPERTYEX('BackupTest','STATUS');
GO
------
RESTORING

 

Lessons learned:

· 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.

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



This entry was posted in Administration, Data Recovery, SQL Server and tagged , , by Marcin Szeliga. Bookmark the permalink.
Marcin Szeliga

About Marcin Szeliga

Since 2006 invariably awarded Microsoft Most Valuable Professional title in the SQL category. A consultant, lecturer, authorized Microsoft trainer with 15 years’ experience, and a database systems architect. He prepared Microsoft partners for the upgrade to SQL Server 2008 and 2012 versions within the Train to Trainers program. A speaker at numerous conferences, including Microsoft Technology Summit, SQL Saturday, SQL Day, Microsoft Security Summit, Heroes Happen {Here}, as well as at user groups meetings. The author of many books and articles devoted to SQL Server.

Comments are closed.