As a working DBA, backing up files is an area that is close to me.
Our Corporate Backup Platform is Symantec\Veritas, let us see how it handles live database files.
“Use Advanced Open File Option” – Unchecked
When creating a job, we can either accept the default option of not taking any special precaution with Opened Files.
In the screenshot below, we left “Use Advanced Open File Option” unchecked.
Our SQL database files are marked “Files is in use“.
“Use Advanced Open File Option” – Checked
On the other hand, when we check the “Advanced Open File Option” option, we are able to decide how we want to handle Opened Files.
The available options are:
- Automatic – Allow VSS to select the snapshot provider
- System – Use Microsoft Software Shadow Copy Provider
- Software – Veritas Storage Foundation for Windows
- Hardware – Use technology provided by hardware manufacturer
We took Option 2 – “System use Microsoft Software Shadow Copy Provider” for a quick drive.
We no longer have skipped files.
We are handling our SQL Server Backups through the built-in SQL Server tools. In essence, we are not quite interested in backing up the live opened SQL Server data and log files.
We will thus exclude those through the Exclusion List option.
The steps are documented are.
And, they are
- Use the menu options ” Edit”\”Manage Selection List”
- Choose the “Excludes” item
Manage Selection List
Exclude Properties ( Before )
Here is the empty list
We click the Insert button
The choices we make in the “Include/Exclude Selections” is detailed below:
- Resource type :- General
- Path :- \\*\*
- The first * means all computers\hosts
- The second * means all drives on the designated hosts
- Selection criteria
- Include subdirectories
Clicked OK button and repeat same steps replacing *.mdf with *.ndf and *.ldf.
Exclude Properties ( After )
Here is our completed list
Again, Backup Exec satisfactory and gracefully handles in-use files.
There are at minimum two avenues for handling them.
The more granular option is to handle them at individual job configuration levels. When handled at the job level, we are able to utilize Microsoft’s Shadow Copy Option or Veritas Storage Foundation.
Also, at the job level, we are able to target specific folders or/and file types.
At a global level, we can also just exclude specific file extensions.