By one of our customer we use Avamar for the backup and restore solution. I was asked by this customer to find a solution to run Avamar backups for a group of databases on a specified instance. In fact, we currently try a database clone solution on some instances and clone’s databases must not be backed up, but the rest of the databases must be.

After some discussions with my colleague I decided to use a PowerShell script which will be called in a SQL job step.
This PowerShell script will select the databases to back-up and use the avsql command to run the backups. Avsql is the command line interface for Avamar and SQL Server, it has plenty of functionalities including operations like browse, backup or restore of an instance.

Prerequisites

There are some prerequisites to use this command line.
The first one is to create an avsql command file where we have to define some parameters for the command we would like to execute:

  • operation to perform
  • id of the user used to run the operation
  • user password (encrypted)
  • Avamar server
  • client where to run the operation
  • retention 60 days

Here a picture of my file:

The second one is to create the user mentioned above.
For that open you Avamar Administrator console, go to Administration, Account Management and search for the client where you want to create the user. In the User Tab, right click on the mouse button and add new user. Select the role “Back up/Restore User”, give a name to your user and enter a password. Once done the user is created:

Job creation

Once done we can connect to the SQL Server instance and create a SQL Agent Job.
I don’t want to waste too much time on job creation as there is no added value with that:

The step will just have to run a PowerShell script which will be executed by the SQL Server Agent Service Account:

The interesting part is more in the script that I copy here:

$instance = 'yourserver\instancename'
$DatabasesList = invoke-sqlcmd -query "select distinct db_name(database_id) as name from sys.master_files where physical_name not like '%SqlClone%'" -serverinstance $instance -QueryTimeout 1000 -ConnectionTimeout 1000 -SuppressProviderContextWarning
Foreach ($Database in $DatabasesList)
{
#prepare database name for Avamar backup
$dbname = $Database.name
$db = "$instance/$dbname"
#prepare command line for Avamar backup
$BackupCmd = "avsql --flagfile=C:\ProgramData\Bis\Avamar\avsql_backup.cmd --brtype=full --label=Daily_Full_Clone_Server """ + $db + """"
#Run backup command via Avamar
powershell.exe -command "Start-Process 'cmd' -verb runas -ArgumentList '/c $BackupCmd'"
}

The trickiest part has been to find the right command to execute the avsql command.
Indeed this command must be executed with elevation via a cmd process.
This part of the script powershell.exe -command “Start-Process ‘cmd’ -verb runas -ArgumentList ‘/c $BackupCmd'” shows how I finally managed it.
I executed a PowerShell command which runs a command prompt with elevation with as argument my Avamar command line.
Once scheduled our job will create Avamar full backup for the selected databases.

I hope this can help Avamar users 😉