SQL point-in-time restores

In this section, we will be performing a point-in-time restore. This operation will use all three of the backup files (one full, two log) to restore to a point in time somewhere before we finalized the last set of INSERT statements that were run against the DatabaseFor-LogBackups database.

When doing a point-in-time restore, we are merely restoring completely all of the log backup files after the full database backup, except the very last log file, where we'll stop at a certain point and, during database recovery, SQL Server will only roll forward the trans-actions up to that specified point. In order to restore to the right point, in this example you will need to refer back to the timestamp value you saw in the output of Listing 5-6, the third data load for the DatabaseForLogBackups database.

Hopefully, you will rarely be called upon to perform these types of restore but when you are, it's vital that you're well drilled, having practiced your restore routines many times, and confident of success, based on your backup validation techniques and random test restores.

GUI-based point-in-time restore

It's entirely possible to perform point-in-time restores via the GUI. On the Restore Database page, we simply need to configure a specific date and time, in the To a point in time: option, rather than accept the default setting of most recent possible. We don't provide a full worked example in the GUI, but feel free to play around with configuring and executing these types of restore in the GUI environment.

Don't worry; point-in-time restores are not as complicated as they may sound! To prove it, let's jump right into the script in Listing 6-3.

The overall intent of this script is to restore the DatabaseForLogBackup full backup file over the top of the DatabaseForLogBackup_RestoreCopy database, created in the previous GUI restore, apply the entire contents of the first log backup, and then the contents of the second log backup, up to the point just before we inserted 100,000 rows into MessageTable3.

USE [master]

GO

--STEP 1: Restore the full backup. Leave database in restoring state RESTORE DATABASE [DatabaseForLogBackups_RestoreCopy]

FROM DISK = N'C:\SQLBackups\Chapter5\DatabaseForLogBackups_Native_Full.bak' WITH FILE = 1, MOVE N'DatabaseForLogBackups' TO N'C:\SQLData\

DatabaseForLogBackups_RestoreCopy.mdf',

MOVE N'DatabaseForLogBackups_log' TO N'C:\SQLData\DatabaseForLogBackups_

RestoreCopy_1.ldf', NORECOVERY, STATS = 10 GO

--STEP 2: Completely restore 1st log backup. Leave database in restoring state RESTORE LOG [DatabaseForLogBackups_RestoreCopy]

FROM DISK = N'C:\SQLBackups\Chapter5\DatabaseForLogBackups_Native_Log.trn' WITH FILE = 1, NORECOVERY, STATS = 10

GO

Chapter 6: Log Restores

--STEP 3: P-I-T restore of 2nd log backup. Recover the database RESTORE LOG [DatabaseForLogBackups_RestoreCopy]

FROM DISK = N'C:\SQLBackups\Chapter5\DatabaseForLogBackups_Native_Log_2.trn' WITH FILE = 1, NOUNLOAD, STATS = 10,

STOPAT = N'January 30, 2012 3:34 PM' , -- configure your time here RECOVERY

GO

Listing 6-3: T-SQL script for a point-in-time restore of DatabaseForLogbackups.

The first section of the script restores the full backup file to the restore copy database.

We use the MOVE parameter for each file to indicate that, rather than use the data and log files for DatabaseForLogBackups as the target for the restore, we should use those for a different database, in this case DatabaseForLogBackup_RestoreCopy. The NORECOVERY parameter indicates that we wish to leave the target database, Database-ForLogBackup_RestoreCopy, in a restoring state, ready to accept further log backup files. Finally, we use the REPLACE parameter, since we are overwriting the data and log files that are currently being used by the DatabaseForLogBackup_RestoreCopy database.

The second step of the restore script applies the first transaction log backup to the database. This is a much shorter command, mainly due to the fact that we do not have to specify a MOVE parameter for each data file, since we already specified the target data and log files for the restore, and those files will have already been placed in the correct location before this RESTORE LOG command executes. Notice that we again use the NORECOVERY parameter in order to leave the database in a non-usable state so we can move on to the next log restore and apply more transactional data.

The second and final LOG RESTORE command is where you'll spot the brand new STOPAT parameter. We supply our specific timestamp value to this parameter in order to instruct SQL Server to stop applying log records at that point. The supplied timestamp value is important since we are instructing SQL Server to restore the database to the state it was in at the point of the last committed transaction at that specific time. We need to use the date time that was output when we ran the script in Chapter 5 (Listing 5-6). In my case,

You'll notice that in Listing 6-3 I added one minute to this time, the reason being that the time output does not include seconds, and the transactions we want to include could have committed at, for example, 2:33:45. By adding a minute to the output and rounding up to 2:34:00, we will capture all the rows we want, but not the larger set of rows that inserted next, after the delay. Note, of course, that the exact format of the timestamp, and its actual value, will be different for you!

This time, we specify the RECOVERY parameter, so that when we execute the command the database will enter recovery mode, and the database will be restored to the point of the last committed transaction at the specified timestamp. When you run Listing 6-3 as a whole, you should see output similar to that shown in Figure 6-8.

54 percent processed.

100 percent processed.

Processed 232 pages for database 'DatabaseForLogBackups_RestoreCopy', file 'DatabaseForLogBackups' on file 1.

Processed 5 pages for database 'DatabaseForLogBackups_RestoreCopy', file 'DatabaseForLogBackups_log' on file 1.

RESTORE DATABASE successfully processed 237 pages in 0.549 seconds (3.369 MB/sec).

100 percent processed.

Processed 0 pages for database 'DatabaseForLogBackups_RestoreCopy', file 'DatabaseForLogBackups' on file 1.

Processed 9 pages for database 'DatabaseForLogBackups_RestoreCopy', file 'DatabaseForLogBackups_log' on file 1.

RESTORE LOG successfully processed 9 pages in 0.007 seconds (9.556 MB/sec).

10 percent processed.

Chapter 6: Log Restores

Processed 0 pages for database 'DatabaseForLogBackups_RestoreCopy', file 'DatabaseForLogBackups' on file 1.

Processed 7033 pages for database 'DatabaseForLogBackups_RestoreCopy', file 'DatabaseForLogBackups_log' on file 1.

RESTORE LOG successfully processed 7033 pages in 1.807 seconds (30.403 MB/sec).

Figure 6-8: Output from the successful point-in-time restore operation.

We see the typical percentage completion messages as well as the total restore operation metrics after each file is completed. What we might like to see in the message output, but cannot, is some indication that we performed a point-in-time restore with the STOPAT parameter. There is no obvious way to tell if we successfully did that other than to double-check our database to see if we did indeed only get part of the data changes that are stored in our second log backup file.

All we have to do is rerun Listing 6-2 and this time, if everything went as planned, we should have 1,020 rows in MessageTable1, 10,020 rows in MessageTable2, but only 20 rows in MessageTable3, since we stopped the restore just before the final 100,000 rows were added to that table.