01-05-2016 12:10 AM
Hi, our customer is using BE 15 on Windows Server 2008 R2 as the backup server. They have a few MS SQL servers (MS SQL 2008/2014 on Windows Server 2008 R2). These servers have two types of BE backups: SDR backups (all local drives are backed up) and MS SQL database backups. According to some Veritas KB, the MS SQL database files should be automatically excluded from the full system backups (SDR backups). However, we tested and found the MS SQL database files in these clients are backed up by the SDR backup jobs.
Does anyone have any idea about this? Are there any pre-requisites to exclude the SQL database files from the backups? Do we need to set anything on the BE sever and/or client to enable this exclusion?
Thanks in advance.
Solved! Go to Solution.
01-05-2016 12:53 AM
For SDR enabled backup, is the Microsoft SQL instance selected in the backup job ?
Also, is this key present on the BE server - https://www.veritas.com/support/en_US/article.TECH92375
Lastly, verbatim from https://www.veritas.com/support/en_US/article.HOWTO24057
For example, .MDF and .IDF files should not be part of the backup because they are opened for exclusive use by the SQL system. These files will be automatically excluded for backup by a feature called Active File Exclusion. If this exclusion did not happen during a non-snapshot backup, these files would appear as in use - skipped. If this exclusion did not happen during a snapshot backup, the files would be backed up in a possible inconsistent state, which could create restore issues.
As per the last 2 lines, exclusion may not happen.
01-05-2016 12:53 AM
For SDR enabled backup, is the Microsoft SQL instance selected in the backup job ?
Also, is this key present on the BE server - https://www.veritas.com/support/en_US/article.TECH92375
Lastly, verbatim from https://www.veritas.com/support/en_US/article.HOWTO24057
For example, .MDF and .IDF files should not be part of the backup because they are opened for exclusive use by the SQL system. These files will be automatically excluded for backup by a feature called Active File Exclusion. If this exclusion did not happen during a non-snapshot backup, these files would appear as in use - skipped. If this exclusion did not happen during a snapshot backup, the files would be backed up in a possible inconsistent state, which could create restore issues.
As per the last 2 lines, exclusion may not happen.
01-05-2016 06:37 PM
Thanks for your replies. We just selected the full drive but did not notice whether the database files are selected or not. However, if I am not wrong, the database files should be automatically excluded from the selections.
So if the exclusion does not happen, what are the possible causes? Thanks.
01-05-2016 07:24 PM
If you select the entire drive, then there is nothing in the selection list to indicate whether the .mdf and the .ldf files are included/excluded. AFE will skip these files when the backup is done.
To verify whether the files are excluded or not, create a restore job and take a look at the directory where these files are located and you would be able to see whether they are included/excluded from the backup.
01-05-2016 10:40 PM
When we do recovery, we found the SQL files are there in the directory, which means these files are backed up by the SDR backup job. So we would like to know if anyone has any idea about the cause to this.
01-05-2016 10:43 PM
01-05-2016 10:53 PM
I read through all the links but none of the links or KBs mention why sometimes the SQL files are not excluded when AFE settings are enabled...
01-05-2016 11:03 PM
Will need to verbose debug (specifically the snapshot part) as to why the SQL files were not excluded by AFE. The above KB mentions this is possible sometimes, however the backed up file will not be consistent.
There can be multiple causes as to why the exclusion did not occur such as -
The AFE registry key is disabled, the SQL VSS writer did not return the exclusions, the SQL files are not locked by the application etc.
01-18-2016 07:32 PM
Thanks a lot for all your replies. We discussed internally and decided to approach Veritas support for assistance when needed since we are still unable to determine the root cause to this.