Operating system excludes

[pheidrich]

Hello,

I am wondering if anybody could help me understand in deep how "Operating System enforced Include/Excludes" work. In particular, when using VSS snapshots of MSSQL database files, but not limited to this case (yes, i know the official way to backup MSSQL is TDP...)

Symptoms: The *.mdf and *.ldf files are excluded from my backup, even though I force the inclusion by a client option set rule "Include *.mdf".

Let me start making the questions, the details come after.

Is it really impossible to backup MSSQL database files using just VSS and B/A client? I don't see any reason not to be so. Microsoft documentation mentions the VSS snapshot of database files using SQL Writer as consistent. There is the explanation from IBM that *.mdf files are excluded by operating system via "NotToBackup" registry key. In fact, *.mdf are not present in this key at all, but they still appear in the final include/exclude list. Another explanation could be that VSS itself enforces this exclusion, but I don't believe in this either. When tracing SQL Writer metadata, all *.mdf appear valid and are included as its components. The "exclude path" field writer metadata is empty.

Did you ever investigate on this? Or, is somebody successfully using the hack with client option set?

How include/excludes work in detail? The manual only talks about dsm.opt, client option sets and NotToBackup registry key. How about priority of these sources?

Here comes my investigation:

1) "query inclexcl" output starts with some directory excludes, then there are my server-enforced client options, finally followed by the rest of rules (from operating system and dsm.opt)

...
Excl Directory C:\programdata\Microsoft\Crypto\RSA\MachineKeys Operating System
Excl Directory C:\Boot Operating System
Excl Directory C:\adsm.sys Operating System
Include All *\...*.ldf Server
Include All *\...*.mdf Server
Exclude All C:\Windows\usrclass.dat.log Operating System
Exclude All C:\Windows\usrclass.dat Operating System
Exclude All C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf Operating System
...

According to this output, my database files should be included in any case, whatever rule comes after. They are not.

2) "dsmc prev backup master.mdf" confirms my thoughts

Preview generated on 04/20/2012 16:27:35.
Name: Size: Type: Status: Pattern: Source: Mgmt Class:
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf 4.00 MB .mdf Included *\...*.mdf Server DEFAULT

3) However, when processing "dsmc selective master.mdf", it is excluded.

Preparing to backup 'c:' using 'VSS' snapshot.
ANS1115W File '\\ondra\c$\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf' excluded by Include/Exclude list

4) I traced the client and in the case of dsmc preview, "Include *.mdf" rule is used, however, in case of selective backup the "Exlude All" operating system rule takes place.

5) When doing VSS snaphosts with other tools (debugging tools, manual snapshot), there is absolutely no problem with *.mdf files. They are included and consistent. Shouldn't it be impossible, once IBM says operating system recommends to exclude these files? Or, the true reason is just to sell TDP for Databases?

Thanks very much for any point!

Pavel

 

[moon-buddy]

In all my years of working with TSM, and when MS introduced VSS, I never successfully backed up 100% of the time a Windows environment with VSS.

Having said that, I would not try using VSS to backup a database even if it is in quiescent state. There is so much inconsistencies using this approach. I am better off (worst case if I don't want to use TDP for SQL) dumping the DB, backing up the dump file and using the dump to restore the DB (although this is just a point-in-time restore).

If I want nearline restores, I will use TDP for SQL.

As to your query why is it that you can't backup MDF files even if you don't exclude it, the exclusion is in the code. Try renaming it and it gets backed up!

 

[pheidrich]

Thanks moon-body. I have very little experience with how reliable VSS is, so I will trust yours.

Saying "the exclusion is in the code" you mean it is hard-coded the client? I am asking because I've heard from several sources that the "client option set hack" does the work.

Next, is TDP's approach so different? I once installed FlashCopy Manager, which TDP is an internal part of, and I understood that it was using VSS too. I my eyes, the only true difference was in the capability of seeing/restoring individual databases, and the GUI of course. Sure, I may be completely wrong...

 

[Harry_Redl]

Hello,

you are asking very good questions ...

Just for the reference - I saw you are asking the same question on developerWorks and you provided more diagnostic info there
https://www.ibm.com/developerworks/forums/thread.jspa?threadID=425888

As far as I understand it, TSM client checks for VSS writers and excludes everything found there (output vshadow.out). The only exception from this are writers used for SystemState backup (but this is not the part of "normal" backup).
I have no (suitable) SQL server at hand but what I found is that there is a Windows service SQL VSS Writer there. If it is running, "vssadmin list writers" shows SQLServerWriter. If it is stopped, this writer disappears from the list.
So the theory is
1) create VSS snapshot using using external tools (pre-backup script?)
2) stop the SQL VSS Writer service
3) use TSM client to backup the SQL databases (using snapshotroot option can help) - as the writer is stopped, it should not be detected
4) start the SQL VSS Writer again

Can you test it and report the results?

Thanks

Harry

 

[Harry_Redl]

Hello,

did a test - seems it is as I thought - when disabling the SQL VSS writer the DB files can be backed up (although the snapshot is created via system VSS writer so it is NOT consistent from the SQL point of view - if you want to play with the creating of SQL consistent snapshot - go ahead and let us know)

IMPORTANT NOTE: I do agree with Ed - for backing up MSSQL use either TDP or SQL maintenance plan to dump the DB to the file. That is supported, verified and working way.

C:\Program Files\Tivoli\TSM\baclient>dsmc
tsm> selective "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\*"
Selective Backup function invoked.

Preparing to backup 'c:' using 'VSS' snapshot.
...
ANS1115W File '\\jrew2k8sql\c$\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DataDB.mdf' excluded by Include/Exclude list
...

Total number of objects inspected:       18
Total number of objects backed up:       10
Total number of bytes inspected:      267.12 MB
Total number of bytes transferred:    131.11 MB
...
tsm> quit

C:\Program Files\Tivoli\TSM\baclient>net stop "SQL Server VSS Writer"
The SQL Server VSS Writer service is stopping.
The SQL Server VSS Writer service was stopped successfully.


C:\Program Files\Tivoli\TSM\baclient>dsmc
tsm> selective "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\*"
Selective Backup function invoked.

Preparing to backup 'c:' using 'VSS' snapshot.
...
Normal File-->       104,857,600 \\jrew2k8sql\c$\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DataDB.mdf [Sent]
...

Total number of objects inspected:       18
Total number of objects backed up:       18
Total number of bytes inspected:      267.22 MB
Total number of bytes transferred:    267.22 MB
...
tsm>

Harry

 

[pheidrich]

Thanks Harry,

I've been recently playing around too. Yes, when SQL writer is stopped the files are not excluded and are backed up, I knew that. It is what one would expect - in such case they are just regular files in the filesystem. The workaround you suggested in your previous post works and I tested it before: Externally create the snapshot and back it up without the writer. However, it is too complicated and generally not a good solution in my eyes.

I give up trying to do it without TDP, it's obvious it is not working reliably. I am just wondering why. With all I currently know and what I've tested about VSS, there is even more confusion around B/A client and its way of using VSS. I am pretty much interested in whether there is a good technical reason, or it's an artificial reason for TDP license

Once more, isn't TDP using exactly the same snapshots, just treating them defferently?

One thing you mentioned in the previous post was new to me. I never realized it was excluding more valid components from several writers (you say all of them that appear in "vshadow -wm2" output, but apparently it's not 100% of them).

I created a testing VSS writer (http://msdn.microsoft.com/en-us/library/windows/desktop/bb530727(v=vs.85).aspx) to protect files "file-to-backup.dat" and "file-not-to-backup.dat". It includes the first as its "component" and excludes the second.

When testing manual VSS snapshot, it behaves correctly as expected. When doing TSM backup the files are excluded. It seems to confirm your idea that B/A client excludes all writers' files...

What the hell is the point of using VSS then?! ;-)

 

[Harry_Redl]

Hi,

well - I think the idea is simple - if it has a VSS writer then it is "high level" application and we should not mess with it.
I think this behavior is hard-coded in the BA-client - not in the API - and this is why TDPs work (they use just the API part, not the BA-client).

Harry

 

[CarlosJ]

Hi all, I have a question related to this, is it possible to edit the Operating System Excludes? We had a DR exercise in these days and Intel Server Support Team is asking us to include C:\windows\system32 in the backups but it is being automatically excluded by "Operating System ", anyway there is no such path in the registry. I saw that TSM Client versions higher to 5.4.1 are excluding this folder so I wonder how to include it, I tried by dsm.opt and Client Option Set but it looks like there is no way to skip or remove "exclude.dir c:\windows\system32" from inclexcl options, any help would be appreciated! Thank you in advance.

 

[pheidrich]

Hi Carlos,

unfortunately, I doubt there is a way to directly edit Operating System Excludes (other than the registry key). My conclusion has been that BA client uses the following sources for include/excludes:

1) dsm.opt + its equivalents (i.e. command line options)
2) client option sets from the server
3) paths from NotToBackup registry key are excluded
4) all VSS writer's components are excluded

The problem is you apparently can't suppress the last one. In theory the client option set should have priority over the rest of sources (and that was the reason why I originally made the question), but it's not working like that in the newest BA client versions. Actually, it works in client GUI, the client option set rules are enforced, but it does not in command line nor scheduled tasks. In my opinion it is a bug or an intentionally created obstacle...

Test the following commands, there you should find out what exactly excludes your files:
- dsmc q inclexcl > includeexclude.out
- diskshadow /l diskshadow.out ---- list writers metadata ---- exit

You can see that C:\windows\system32 is component of many system writers. The BA client asks all active writers about their components and excludes them.

------------------------

Anyway, I forgot to mention the most important fact: C:\windows\system32 is a system folder and as such it is backed up with the "System State".

See:
http://pic.dhe.ibm.com/infocenter/t...ibm.itsm.client.doc/c_bac_bkupunderstand.html
http://pic.dhe.ibm.com/infocenter/tsminfo/v6r3/topic/com.ibm.itsm.client.doc/t_bac_sysstate.html