How to perform a SQL point in time restore with UDP

This week Arcserve released update 4 for UDP 6.5, and this is a great new update that comes with lots of new features and enhancements (see our release notes here) For this post I wanted to highlight a feature that was high on the wish list, which is SQL Point in Time (PIT) restores.

In previous releases, UDP took a snapshot of the SQL Database and you were able to recover from these snapshots. With Update 4, you can now restore to a point in time between the recovery point snapshots taken by UDP.

In this post I will explain how to do perform a PIT restore.

Pre-requisites:

Before you start with PIT restores there are two things required;

  • UDP Agent, in this release, PIT restore only works with SQL servers that have an agent installed and backup is taken via this agent. If you are using agentless backup and would like to use PIT, then I would recommend installing the agent and change the plan for this server.
  • Database recovery model should be configured as “Full” or “Bulk-Logged” this can be configured on the properties of the database in the SQL manager

In my lab environment I have a SQL server installed and the first step is to enable PIT on the SQL server. PIT is a command line based tool which for most SQL administrators is easy to understand and to work with.

To enable PIT, open an command prompt on the SQL server and go to the Arcserve BIN folder C:\Program Files\Arcserve\Unified Data Protection\Engine\BIN and start the PIT utility (pit.exe)

Next to enable PIT backup type the command;

set pitbbackup=1

Once done, run an agent-based backup of this server and once the backup is completed the PIT related metadata is created in the catalog for this server.

The next step is to find the SQL server identifier used by Arcserve UDP.  You will need this to query the PIT logs.

To check your server identifier do a dir on the RPS datastore (which can be done remotely from the SQL server)

Dir \\[RPS SERVER]\[DATASTORE]

My SQL server Identifier is highlighted in the image above.

To check the latest catalogs created, you can perform a remote directory search on the catalog from the sql server. The format of the catalog path is:

Dir \\[RPS SERVER]\[DATASTORE]\[SERVER IDENTIFIER]\CATALOG

To see the metadata create you can list the catalog created and in my scenario that is S0000000024

I can see that the metadata is created and that the first PIT based backup of this server was successful.

So now I can start making some changes in my SQL databases and I start with listing a table called member in my AdventureWorks2014 database, I will list the starting point with the following image

A simple list shows me some members inside this table and I will start working on this SQL database and make some changes, I will add two new records and remove one record called Charlie

Once done, I will drop my table as part of this test, so that this database is not complete anymore

At this point we will perform a second backup which will create a new catalog and this catalog will hold all the changes made earlier.

If I look into my catalog structure I can see a new catalog has been created, in my case S0000000025

And when I list this catalog I can see the new PIT files created

To start a recovery, we will first need the PIT utility, first of all we will enable the PIT restore by typing

set pitrestore=1

next we can query the latest PIT catalog by typing

query \AdventureWorks2014 \\rps\udp_lab-0000\sql[321d80a2-454a-4f3b-86fc-d4f2c3bd7a09]\Catalog\S0000000025

The output of this query provides me all the changes made in between the recovery points taken by UDP.

For this demo, I want to go back to the point before I deleted Charlie and dropped the table in my SQL database.

In the PIT tool I will set the restore time on point 15 (in my image above) in my query which is the delete operation

To set the this time type

set pittime=”[date & time stamp of record from which you want to restore]

Note: Mark and paste works similar as putty in the PIT utility

Now this is done, the next step is to start the restore wizard.

Open the agent restore wizard, or start restore wizard from the UDP console and on the restore it is important to select the previous incremental backup!

I selected the previous incremental backup. The database and click next

Restore to the original location and press next

In the summary screen click the finish button to start the restore

Once the restore is done, you can check the restore job log to make sure all went as planned

And next is to check the database itself, I ran my SQL query and I can see my table is back again but also that the Charlie record is back again.

I hope you found this post useful and if you have any questions ping me a DM or leave a comment.

Cheers,

Harold

Please follow and like vDutchy:
LinkedIn
FACEBOOK
Google+
Google+
http://vdutchy.com/how-to-perform-a-sql-point-in-time-restore-with-udp/
RSS
Follow by Email

One comment

Leave a Reply