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:
So there you have it – point in time recovery for SQL Server. We think that you will agree that this is a huge time saver. So just to summarize Clumio delivers the following value to organizations:
Figure 7: Clumio Value Propositions
So who are the targeted customers?
- Customers who don’t want to deploy anything on their SQL servers. This will work for them as this is a completely agentless approach.
- Customers who love simplicity as with Clumio backups, especially since the Full restores are enabled out of the box.
- Customer’s DBAs who want to continue protecting the SQL servers with the proven and familiar methodologies
- Customer’s DBAs would love the convenience factor for the point in time recoveries can be performed as all the backups are present locally in the Virtual SQL server, which makes it very convenient for DBAs to perform the point in time recovery.
- Customer’s DBAs would love the convenience as very easy automation like expiry scripts, the point in time recoveries can be performed.
- Customers who want to have very low RTO given our patented Rapid restore technology.We hope that you found these How To videos helpful. More to come.