How To: Point in Time Recovery for SQL Server (Part Two)

It’s demo day! Hopefully you found my earlier demo (Protecting SQL-Server on Clumio Using a Single-pass Backup) helpful. Today, I will demonstrate point in time recovery for SQL Server with Clumio™. If you are not familiar with Clumio, it’s the industry’s first cloud native data platform to backup heterogeneous cloud data sources in one service, on one single pane of glass, and with one customer experience.

As a reminder, the prerequisites for the demo are:

• Volume Shadow Copy Services (VSS) that must be running on the servers, and
• VMTools that has to be installed and
• vSphere version >= 6.5.

So let’s dive in and look at how to perform a point in time recovery for SQL Server. For this demo, let’s assume that we performed a full backup at the beginning of the cycle and then continue with Differential backups, as shown in figure 1.

Figure 1: Backup methodology with full backups and differential backups using Clumio

SQL Server database’s “backupset” table will be populated as shown in figure 2 when we use this methodology. All these backups should be stored locally in the virtual machine.

Figure 2: Backupset table

Also, there is a “reference” table that is tracking the backups performed. Every time a backup is performed, this table gets updated with the corresponding backup information. The reference table is shown in Figure 3. For example, when the full backup is performed, this reference table gets updated as full backup.

Since we are doing point-in-time recovery, which allows us to restore a database into a state it was in any point of time, here in the demo we are going to recover this reference table till “Day 2 Transaction Backup 3” record (See Figure 3).

Figure 3: Reference table

Once Clumio restores the virtual machine, all the backups are present locally. This is a big advantage with this methodology. Since the backups are present in the restored virtual machine’s local file system, it is super easy to perform a point in time recovery. See figure 4, which shows the backups in the local file system with full backups and differential backups.

Figure 4: Local backups in the restored virtual machine

After the virtual machine is restored in the SQL Server Management Studio, the following options will need to be configured for the database we want to restore. First we need to set “Overwrite the existing database” with that done, now we make sure that we deselect “Tail-Log backup.” It is not required (Shown in figure 5).

Figure 5: Restore options

Then we simply select the backup sets from the local file system and remove the ones which are not required. You can see this in figure 6. Since we are only performing the recovery until “day 2 transaction log backup 3,” we should deselect “transaction log backup 4 of day 2.”

Figure 6: Backup sets to restore

After the restore, the reference table should be showing records up to “day 2 transaction log backup 3” in the table. That’s it. We have successfully completed a point in time restore for SQL Server. It is that easy with Clumio’s cloud native data platform. You can watch the full demo I just walked through in the video below: