SQL maintance file Command Line Backup

configure the backup job via GUI, and run it to make sure it can work.

open the backup log and extract the complete avtar command on the top of the log.

put the extracted avtar command in an OS script and schedule it using OS tool by running it after DB flat file is created.

please mark my answer as "correct/helpful answer" if it helps.

thanks guys i will give them a try and update

Chris

HI when we try and run the nothing happens

from the top the command line says

avtar --sysdir="C:\Program Files\avs\etc" --bindir="C:\Program Files\avs\bin" --vardir="C:\Program Files\avs\var" --ctlcallport=4237 --ctlinterface=3001-Windows-XH_SQL_TL-Snapper_TL-1377610200100 --logfile="C:\Program Files\avs\var\clientlogs\XH_SQL_TL-Snapper_TL-1377610200100-3001-Windows.log"

our apps support team prefer to use the SQL maintenance schedule to dump the database to a flat file, it is the way they have always done it

I can set up a policy to back it up, it is not a problem i was just trying to automate it, so as soon as the maintenance schedule had finish it would get backed up to avamar and the same goes with the SQL transaction logs

The problem with trying to copy and paste the avtar command from a server-initiated backup is that these backups use a protocol called "CTL" to pass commands between avagent and avtar (you can see the --ctlcallport and --ctlinterface in the call pasted above, for example). This won't work unless avagent launched the backup because the agent won't be listening for CTL messages from avtar.

Is there a specific reason you are not planning to use the SQL plug-in? This is likely your best option. The SQL plug-in backs up the databases to the Avamar server in the standard Microsoft backup format so they can always be restored to disk using avtar and imported through the Microsoft SQL Studio interface.

If you're unable to use the SQL plug-in, the next best option would be to set up the flat file dump as a pre-script for scheduled filesystem backups so the database is dumped before the backup starts.

Running manual avtar backups is not recommended because these backups can't be monitored or managed through the Avamar Administrator GUI or mccli.

If you want the backup job to show up in the Activity monitor in the GUI, you could probably set up a script that makes an SSH connection to the utility node and uses mccli to kick off the backup. Something like plink (or its more GUI-friendly cousin TortoisePlink) might work for that.

If you do need to run a manual backup of the flat files, the general syntax for avtar is similar to the syntax for tar, e.g.:

tar -cvzf tarfile.tgz [file2 file3 ...]

avtar -c --server=avamar1.example.com --path=/clients/sql-server-1.example.com --id=backuponly --password=backuponly1 [file2 file3 ...]

--server is the hostname or IP address of the Avamar server's utility node

--path is the client account where the backup should be stored

--id is the username to use for server login

--password is the password to use for server login

List the files and / or directories you want to back up one by one on the command line.

You may find flags like --label useful for identifying backups. You can get a list of command line options using avtar --help but be cautious about using flags if you don't know what they do as this may cause unexpected results.

Unfortunately there are monitoring challenges with this since the Avamar Administrator Server won't be aware of the backup.

This post is a little older but is exactly the same thing we have done in our previous backup software, and something I wanted to continue with Avamar.  When we were looking at Avamar last year (and began implementing Avamar right about this same time last year – 2013) this was one of the questions we asked if we could perform this type of task.  In our previous backup software they referred to it as a 'Trigger'.  The trigger exe (installed standardly with the backup client) was specified in the maintenance job with a pre-defined 'Trigger Name' the backup administrator configured in the backup job (ME :)).  When this exe was called the backup server would see this request, see what the trigger name argument passed was and kick off the corresponding job on the backup server – that worked really well.  We were told that yes we could do this with Avamar via the avagent.  But after reading what Ian said, you can but it isn't exactly recommended, and a little harder to monitor.

      I took Ian's suggestion and created a script that would ssh into the Avamar server and kick off a backup group that is configured to back up the directory I had specified.  Within a couple of days this script has blown up and a lot more functional and so far seems to work really well.    Basically now all I have to do is make sure the sql server service account is in a couple of security groups.  The database admin’s point to a batch file with one argument, in this case what environment the server is in ex. DV, DR PD or QA.  From there I have the script to check for a file with the ssh commands to execute to ssh into the Avamar server and kick off the backup job. The script will ssh to the Avamar server to cache the pesky host key – and will cache it.   If the file I mentioned earlier is missing then theoretically the server has not been configured for triggers yet, the script in turn will build a different file temporarily to create the dataset, group and add the client to the group and then kick off the backup job on the fly.  So far it is working out really great. The database guys like the fact they can at least see the Avamar trigger job in the avagent console history, I have the naming convention setup in a way to see which are triggers, and if that job were to fail we would know quicker if the backups were backed up or not.  With the standard file system backups they would not necessarily know that easily; they kick off the sql maintenance job and they see their job is complete but would not know if Avamar got the backup files afterwards.  I plan on configuring a report to have automatically emailed to the dba’s showing all the trigger jobs using Avamar or DPA for the reports.

We have looked at the SQL server plugin briefly.  We are kind of in the same boat in that this is how we have always done this and it has worked.  But we are going to look at the plugin again and see if we would want to use it.  At the time we were deploying Avamar and figured it would be easier to at least get things how we had it before, and then come back and do some customizations. Although some issues I have into before in the past with plugins with our previous backup software.  For Exchange for an example, we had to retrieve some data a couple of years prior from an Exchange backup done via an Exchange plugin – don’t ask me why we had data that old….that is a whole other can of worms.  But at that time we were running exchange 2007, we are now running 2010.  I had some issues at first getting the restore to work because that long ago our Backup server was running a different version.  The backup client on the server was version XX, and then the plugin was version XX.  I had to think back and somewhat tinker around until I got the right backup client version installed, the right exchange plugin installed for the version of Exchange we were running at that time.  Typically with the flat file backups, like for SQL, as long as we were able to see it, the file system plugin’s always work. 

That is a lot but I figured I would post what I have found out and if anyone else came along wanting the same thing maybe this would give them some ideas.  I will forewarn the readers of this that I did not go back and check for good punctuation and working.  I typed this up and checked for a few spellings and posted it.  For the most part it should somewhat make sense

Something I meant to mention in the last post.  In the script I currently have the admin account username and password configured to ssh to the avamar nodes.  I want to be able to use certificates to authenticate to the Avamar nodes instead of the admin credentials.  I am a little foggy on the certificate stuff for Linux but it looks like there are already certificates created.  I have tried using the public certificate from /home/admin/.ssh but plink states it is invalid - or something along those lines, I can't remember off the top of my head.  Does anyone know what certificate to use, or do I need to generate new ones?  I didn't want to generate any until I knew it won't affect Avamar functions.

Thanks for writing this up!

If you want to connect to the Avamar server using key-based authentication, it's the private key you need. You can use the existing /home/admin/.ssh/dpnid key but to use it with Plink, you'll probably have to convert it to PuTTY format first. You can convert the key using PuTTYgen (Conversions => Import Key).

Ian, thanks for the suggestion.  Converting the cert to putty format worked like a charm!  I like that a lot better.  I was previously trying to use the admin_key, but not converted of course.  So the dpnid is the private key?  What is the admin_key?  The public for admin generated from dpnid?

There are two keypairs on the Avamar server -- one is admin_key and the other is dpnid. Each keypair has a public key and a private key. The private key is required to make connections to remote hosts and should be kept secured. Having a copy of the private key permits users to log into the Avamar at an OS level which you probably don't want.

Having two keypairs is mainly a historical artifact. The main differences are that the admin key requires a passphrase and restricts login to only the admin user. If you wanted to use the admin key in a script, you'd have to strip the passphrase (which can also be done using PuTTYgen).

Ideally, you should probably set up a separate RHEL or SLES system on which you can install the mccli package and run mccli commands, then generate SSH keys on the various client systems to control access to the mccli host. This is better not just from an access control perspective but also because there's less risk of overwhelming the utility node with mccli requests.