Restore to test servers using Powershell cmdlets

[Alex1983]

Hi guys, I am pretty new to powershell, so please forgive me if this question seems a bit ignorant...
We are trying to restore backups to our test environments from our IT teams Veeam backup images, at the moment we have to spin up the entire image, perform the backups and then copy them to a location made available to our other test servers, i found the below cmdlets on the veeam forums and thought this might be what we needed, however, i have come across a stumbling block:

When running

Code: Select all

Get-VBRSQLDatabases -ApplicationRestorePoint $restore_point -Name $db_name

where $restore_point =

Code: Select all

Get-VBRApplicationRestorePoint -SQL -name $source_vm | ? {$_.type -eq "Full"} | Sort-Object creationtime -Descending | Select-Object -First 1

I get the error

Get-VBRSQLDatabase : A database with the name [db_name] does not exist for restore point...

Could this perhaps be due to the way our IT team is performing there backups or is this code related and i am just missing something?
Any advice here would be greatly appreciated, thanks!

[nielsengelen]

Did you enable application aware processing in those backup jobs? In which database mode are the databases you are trying to perform this one?

[Alex1983]

Turns out that option was not enabled, I have enabled it now but now i am unable to see the restore points within the job that i created?
If i run

Code: Select all

Get-VBRJob

I can see the job in that list, but when i run

Code: Select all

Get-VBRRestorePoint -backup "job_name_here"

i get the below error:

Get-VBRRestorePoint : Cannot process argument transformation on parameter 'Backup'. Sequence contains no matching element.

[Alex1983]

My apologies, I found out that the IT department had changed the name of the job, so I am now able to see the restore points within the job, unfortunately I am still struggling with the below

Code: Select all

Add-PSSnapin VeeamPSSnapIn -ErrorAction SilentlyContinue

$source_job_name    = "job_name"
$source_vm          = "source_instance_name"
$source_db_name     = "database_name"

$target_vm          = "vm_name"
$target_credentials = Get-VBRCredentials -Name "Domain\my_user_name"
$target_instance    = "target_instance_name"

$target_database    = "{0}-{1}" -f $source_db_name, (Get-Date -Format yyyyMMdd) ##restoring database with different name for testing purposes

$restore_point = Get-VBRRestorePoint -Backup $source_job_name | ? VmName -match "^$source_vm" | Sort-Object creationtime -Descending | Select-Object -First 1
##$restore_point = Get-VBRApplicationRestorePoint -SQL -name $source_vm | ? {$_.type -eq "Increment"} | Sort-Object creationtime -Descending | Select-Object -First 1

try 
{
    $database = Get-VBRSQLDatabase -ApplicationRestorePoint $restore_point -Name $source_db_name
} 
catch 
{
    "Could not find database" 
    break
}


Start-VBRSQLDatabaseRestore -Database $database -ServerName $target_vm -InstanceName $target_instance -DatabaseName $target_database -GuestCredentials $target_credentials -SqlCredentials $target_credentials

I still get the error 'Could not find database'
Application aware processing is enabled and the databases are all in Simple mode.

[veremin]

In simple mode SQL itself manages log truncation. So, there is no Veeam SQL log truncation job for dbs in simple mode, neither there is a point-in-time restore. Thanks.

[Alex1983]

I am not trying to do a point in time recovery, i am attempting to restore the entire database and hopefully an entire SQL instance.

[Alex1983]

Ok, i took what you said into consideration and realized that in order to restore one database Veeam would have to be performing transaction log backups on the source instance in order to obtain a valid restore point, the below code seems to work for me perhaps it could help someone else who finds this post:

Code: Select all

Add-PSSnapin VeeamPSSnapIn -ErrorAction SilentlyContinue
Connect-VBRServer -Server "veeam_b&r_instance"

$source_job_name    = "job_name"
$source_vm          = "source_vm"
$source_db_name     = "source_database"

$target_vm          = "target_vm"
$target_credentials = Get-VBRCredentials -Name "domain\user_name"
$target_instance    = " " ##stays blank if the target vm has a default instance

$target_database    = "{0}-{1}" -f $source_db_name, (Get-Date -Format yyyyMMdd) ##added date at end of db_name for testing purposes

$restore_point = Get-VBRRestorePoint -Backup $source_job_name | ? VmName -match "^$source_vm" | Sort-Object creationtime -Descending | Select-Object -First 1
##$restore_point = Get-VBRApplicationRestorePoint -SQL -name $source_vm | ? {$_.type -eq "Increment"} | Sort-Object creationtime -Descending | Select-Object -First 1

try 
{
    $database = Get-VBRSQLDatabase -ApplicationRestorePoint $restore_point -Name $source_db_name
} 
catch 
{
    "Could not find database" 
    break
}


Start-VBRSQLDatabaseRestore -Database $database -ServerName $target_vm -InstanceName $target_instance -DatabaseName $target_database -GuestCredentials $target_credentials -SqlCredentials $target_credentials

Disconnect-VBRServer | out-null

Now, if someone could perhaps tell me if restoring an entire SQL instance would have the same limitations that would be very helpful, I will continue attempting on my end and update this post should i find a solution before someone replies to this.

Thanks for the assist!

[inoisikoiski]

Help please. I am getting the error the following error when I try to restore a database that is in simple mode even when i try to change the restore points. The script perfectly works well if the database is in full recovery mode.

$source = Get-VBRApplicationRestorePoint -SQL -Name "testsql"
Get-VBRSqlDatabase -ApplicationRestorePoint $source [0] -Name "testsqldb"

Get-VBRSQLDatabase : A database with the name testsqldb not exist for restore point

Anyone who encountered this one before?
Thanks!

[veremin]

Your post has been merged into existing discussion. Please, check the answers provided above and ask for additional help (if necessary). Thanks

[inoisikoiski]

Unfortunately, the script from Alex1983 didn't work for me. It seems like it's with how the way the backup is setup to our servers because when I do the below to one of our SQL servers

get-vbrsqldatabase -applicationrestorepoint $server[0]

I get a list of databases on the restore point regardless of recovery model.

My question is why it doesn't work for other SQL servers.

Thanks

[veremin]

Just spotted that you're using obsolete cmdlets, can you check whether the issue persists with the new ones? Thanks!