Highlighted
vbigchris
6 Indium

SQL maintance file Command Line Backup

HI Guys

I am new to the whole Avamar thing so please forgive Me if this has already been posted

We are not going to use the Avamar SQL plugin to backup our SQL databases, we use a maintenance schedule to dump the DB backup to a flat file

so my question is, is there a way to instruct avamar to backup up a the flat file once it have been created via command line?

thanks in advance

Chris

13 Replies
8 Krypton

Re: SQL maintance file Command Line Backup

Hi Chris,

You could do this with pre and post commands/tasks in MCS for your client or to involve a script on client side.

Rej

0 Kudos
8 Krypton

Re: 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.

0 Kudos
vbigchris
6 Indium

Re: SQL maintance file Command Line Backup

thanks guys i will give them a try and update

Chris

0 Kudos
vbigchris
6 Indium

Re: SQL maintance file Command Line Backup

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"

0 Kudos
8 Krypton

Re: SQL maintance file Command Line Backup

well, the avtar command might have some inappropriate parameter value here such as logfile because it should be generated by the command itself other than being specified manually, so my suggestion for now is organize your own avtar command referring to the command guide and give it a try. good luck.

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

0 Kudos
8 Krypton

Re: SQL maintance file Command Line Backup

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.

0 Kudos
vbigchris
6 Indium

Re: SQL maintance file Command Line Backup

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

0 Kudos
8 Krypton

Re: SQL maintance file Command Line Backup

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 <file1> [file2 file3 ...]

avtar -c --server=avamar1.example.com --path=/clients/sql-server-1.example.com --id=backuponly --password=backuponly1 <file1> [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.

8 Krypton

Re: SQL maintance file Command Line 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