Restoring a SQL Database with VDP Advanced

In a previous article I showed how to backup a SQL database with the VDP Advanced agent.  This article is going to show you how to restore a database from backup in the event that you accidentally deleted the database or want to restore over the top of the existing database.  For this test I created a new database called TestDB in simple recovery mode and created a table called dbo.Test:

I then performed an application backup of the SQL server to capture the newly created database:

Performing a restore of a deleted database

In the first example I am going to delete the database and then perform a restore from backup.  As you can see, I've now deleted the database from the SQL server:

In order to perform a restore simply navigate to the VDP plugin and click the Restore tab and navigate down the tree structure until you find the database that you wish to restore.  Check it and then click the Restore button:

Verify the database that you have selected is correct and click Next:

Leave the Restore to original location check ticked and expand the Advanced Options and uncheck the Tail-log backup option.  The tail-log backup option is only used for databases that use the full or bulk-logged recovery model.  Once finished click Next:

Verify your settings and click Finish:

The restore job should start and you can monitor this via the Tasks pane within the web client.  Once it's completed you should see the database has been restored with all data (Refresh SQL Management Studio if you left it open):

Performing a restore over an existing database

In this example I'm going to show you how to restore a database over the existing database.  This is handy if you've accidentally deleted some data or some data is potentially corrupt.  For this example I'm going to delete the dbo.Test table and then perform the restore over the top of the existing database.  As you can see the table has been deleted:

Perform exactly the same steps as per the previous example and then monitor the job to ensure it completes successfully and then referesh the view in SQL Management Studio and you should see the returned data:

If you are experiencing any issues with the restore then check out the log files on the client at the following location:

C:\Program Files\avp\var

Below is an example of a log file with a successful restore:

<logheader platform="Windows Server 2012 Datacenter Server Edition (No Service Pack) 64-bit" process_id="avsql" version="7.0.182-99" tz="GMT Daylight Time" />

--------------------------------------------------------------------------------------------------------
-----  START avsql log 2014-04-25 10:54:41 GMT Daylight Time  [7.0.182-99 Windows Server 2012 Datacenter Server Edition (No Service Pack) 64-bit-AMD64]
--------------------------------------------------------------------------------------------------------

2014-04-25 10:54:41 avsql Info <5008>: Logging to C:\Program Files\avp\var\MOD-1398419682252-3006-SQL.log
2014-04-25 10:54:41 avsql Info <6673>: CTL listening on port 56549
2014-04-25 10:54:41 avsql Info <10684>: Setting ctl message version to 3 (from 1)
2014-04-25 10:54:41 avsql Info <16136>: Setting ctl max message size to 268435456
2014-04-25 10:54:41 avsql Info <17376>: Working on stand alone environment.
2014-04-25 10:54:41 avsql Info <7838>: - Adding user exclude "*/tempdb"
2014-04-25 10:54:42 avsql Warning <14282>: Option "Handle SQL services automatically" can only be used with option "Restore system databases."
2014-04-25 10:54:42 avsql Info <18824>: Compare hostname 'sql01.m80arm.local' with current machine hostname 'sql01'.
2014-04-25 10:54:42 avsql Info <7062>: Targets count 1
2014-04-25 10:54:42 avsql Info <7063>:     target[0] = '(local)/TestDB/f-0'
2014-04-25 10:54:42 avsql Info <7065>: Working on target '(local)/TestDB/f-0'
2014-04-25 10:54:42 avsql Info <14293>: Creating a database task for '(local)/TestDB/f-0' (hcount 328)
2014-04-25 10:54:42 avsql Info <14245>: Creating a thread pool with 1 worker threads.
2014-04-25 10:54:42 avsql Info <14248>: Executing task: (local)/TestDB/f-0 --> vd_streams[1]
2014-04-25 10:54:42 avsql Info <14250>: VDI SQL Instance: 
2014-04-25 10:54:43 avsql Info <14266>: Executing the data transfer task: (local)/TestDB/f-0.stream0
2014-04-25 10:54:43 avsql Info <9552>: Spawning 'C:\Program Files\avp\bin\avtar' (avtar --case_sensitive="false" --max-streams="1" --cacheprefix="avsql_t0" --ctlcallport="56549" --ctlinterface="3006-MOD-1398419682252#1" --check-stdin-path="false" --logfile="C:\Program Files\avp\var\MOD-1398419682252#1-3006-SQL.avtar.log" --vardir="C:\Program Files\avp\var" --bindir="C:\Program Files\avp\bin" --sysdir="C:\Program Files\avp\etc" --acnt="/clients/VDPApps/sql01.m80arm.local" --id="restoreonly" --ap="****************" --server="vdp.m80arm.local")
2014-04-25 10:54:43 avsql Info <6686>: Process 2552 (C:\Program Files\avp\bin\avtar) for workorder MOD-1398419682252#1 started
2014-04-25 10:54:43 avsql Info <10684>: Setting ctl message version to 3 (from 1)
2014-04-25 10:54:43 avsql Info <16136>: Setting ctl max message size to 268435456
2014-04-25 10:54:43 avsql Info <7732>: Waiting for avtar to finish
2014-04-25 10:54:43 avsql Info <6688>: Process 2552 (C:\Program Files\avp\bin\avtar) finished (code 0: success)
2014-04-25 10:54:43 avsql Info <6446>: SQL VDI transfer of '(local)_TestDB_MOD-1398419682252-3006-SQL' to '(local)/TestDB/f-0' successful
2014-04-25 10:54:43 avsql Info <14275>: There is no more tasks to process.
2014-04-25 10:54:43 avsql Info <14271>: Waiting for worker threads to finish ...
2014-04-25 10:54:43 avsql Info <16256>: Creating process summary ...
2014-04-25 10:54:43 avsql Info <16257>: Inserted task: 1, completed: 1, aborted: 0.
2014-04-25 10:54:43 avsql Info <7271>: Final summary generated subwork 1, cancelled/aborted 0, snapview 1, exitcode 0
2014-04-25 10:54:48 avsql Info <6451>: avsql returning with exitcode 0

--------------------------------------------------------------------------------------------------------
----- END avsql log 2014-04-25 10:54:48 GMT Daylight Time  (1 warning, 0 errors, 0 fatal errors)
--------------------------------------------------------------------------------------------------------