Unsolved
This post is more than 5 years old
1 Rookie
•
21 Posts
0
1796
March 24th, 2009 07:00
NMSQL not making incremental backups when file backup is using VSS
I know this case has been up before but haven't seen any good answers. Maybe there isn't one?
When I have VSS enabled on any MSSQL client, incremental backup of MSSQL is always promoted to full backup if file backup (with VSS enabled) was made after the last NMSQL backup. I tried to add VSS:MSDEWriter=off to Save operations attribute (as stated in esg76468) for the file backup but that didn't make any difference.
When I set the attribute "Save operations" to VSS:*=off for the file backup, incremental backup is made correctly.
At 16:30 we are backing up the filesystem (full on thursdays and incremental all other days) using Networker Client for Windows (that includes VSS technology). I have a directive that skips *.ldf *.mdf etc.. The attribute "Save operations" are blank to enable VSS technology.
At 20:30 we are taking SQL Server backup (full on thursdays and incremental all other days) with the Networker Module for SQL Server. Due to the VSS file backup with the Networker Client for Windows at 16:30 the incremental backup of the SQL Server is always promoted to FULL backup instead of incremental.
If I turn off VSS (VSS:*=off) for the 16:30 backup that uses Networker Client for Windows the incremental backup of SQL Server works correctly and is not promoted to full backup.
I want to be able to make a file backup with VSS technology with Networker Client for Windows but still be able to use the incremental functionallity with the Networker Module for SQL Server.
Any ideas?
Networker server is using Windows Server 2003 R2 x86 and Networker 7.3 SP4 Build 558.
SQL Servers are using Windows Server 2003 R2 x86 or Windows Server 2003 R2 x64 with Networker 7.3 SP4 Build 558 and NMSQL 5.2.0.71.
Ps. I have submitted a case to support, and got the answer that it isn't supported. You have to use the Networker Module for Microsoft Applications. But later I got the impression that they thought I was trying to backup the filesystem with NMSQL. While I'm clarifying that I thought I would get your opinion. Ds.
When I have VSS enabled on any MSSQL client, incremental backup of MSSQL is always promoted to full backup if file backup (with VSS enabled) was made after the last NMSQL backup. I tried to add VSS:MSDEWriter=off to Save operations attribute (as stated in esg76468) for the file backup but that didn't make any difference.
When I set the attribute "Save operations" to VSS:*=off for the file backup, incremental backup is made correctly.
At 16:30 we are backing up the filesystem (full on thursdays and incremental all other days) using Networker Client for Windows (that includes VSS technology). I have a directive that skips *.ldf *.mdf etc.. The attribute "Save operations" are blank to enable VSS technology.
At 20:30 we are taking SQL Server backup (full on thursdays and incremental all other days) with the Networker Module for SQL Server. Due to the VSS file backup with the Networker Client for Windows at 16:30 the incremental backup of the SQL Server is always promoted to FULL backup instead of incremental.
If I turn off VSS (VSS:*=off) for the 16:30 backup that uses Networker Client for Windows the incremental backup of SQL Server works correctly and is not promoted to full backup.
I want to be able to make a file backup with VSS technology with Networker Client for Windows but still be able to use the incremental functionallity with the Networker Module for SQL Server.
Any ideas?
Networker server is using Windows Server 2003 R2 x86 and Networker 7.3 SP4 Build 558.
SQL Servers are using Windows Server 2003 R2 x86 or Windows Server 2003 R2 x64 with Networker 7.3 SP4 Build 558 and NMSQL 5.2.0.71.
Ps. I have submitted a case to support, and got the answer that it isn't supported. You have to use the Networker Module for Microsoft Applications. But later I got the impression that they thought I was trying to backup the filesystem with NMSQL. While I'm clarifying that I thought I would get your opinion. Ds.
No Events found!
ble1
4 Operator
•
14.3K Posts
0
March 24th, 2009 07:00
This is known - I wonder what your support was talking about
You could try to disable VSS on OS level - just bring down VSS service.
The issue really is that even with VSS:MSDEWriter=Off VSS framework will do something that SQL will see as (fake) backup which causes this. Of course, if you use 7.3.1 client or 7.2.2 client you get things to work correctly too - isn't that interesting?
Bottom line is that according to MS rules 3rd party products need to call all writers that have data on that volume. Putting it Off won't disallow call to writer, but rather not backup data by that writer in case writer would offer something. This causes fake backup situation. MS is aware of that and I believe following article is talking about this too: http://support.microsoft.com/kb/951288
vallviksbrukab
1 Rookie
•
21 Posts
0
March 25th, 2009 04:00
Did a little more digging. There is a service called "SQL Server VSS Writer". I stopped and disabled the service after reading this article http://msdn.microsoft.com/en-us/library/ms175536.aspx (it was linked to from the article you gave me). Figure this service was messing things up. The article mentions that the MSDEWriter would be activated if SQL Writer is not enabled.
"Starting with SQL Server 2005 installation, SQL Writer is the preferred writer,
though MSDE Writer will continue to work and will be the default writer if installed
and SQL Writer is not enabled. To start and use the SQL Writer, first disable the
MSDE writer from enumerating SQL Server 2005 databases."
I figured if I disable SQL Writer the MSDEWriter would kick in. If it that happened I would have to try the "VSS:MSDEWriter=off" again. But then I read this article http://support.microsoft.com/kb/919023. It says that, to determine which writer is configured by default as the writer for the instance of SQL Server, check the value under the registry key "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\VSS\Settings\MSDEVersionChecking". For SQL Server 2005 (see table in article) a value of 0 means that MSDEWriter is default, a value greater than 0 means that SQL Writer is default. Looking at this blog http://sqlserver-qa.net/blogs/tools/archive/2008/02/19/sql-backup-sqlwrite-for-best-practice.aspx it seems that SQL Server 2005 SP2 will activate SQL Writer by default, disabling MSDEWriter. We are running SQL Server 2005 SP3 on the SQL Server boxes. My conclusion is that if I disable the service "SQL Server VSS Writer" no Writer will be used to evaluate SQL Server databases and NMSQL will work as expected (hopefully).
Trial and error
---------------
1. Stopped and disabled SQL Server VSS Writer.
2. Search the entire registry for the key "MSDEVersionChecking", noting that it has a value of 1 (meaning, use SQL Writer).
3. Did a full file backup using All saveset with the attribute "Save operations" empty, enabling VSS.
4. Did a full backup of the SQL Server using MSSQL: saveset.
5. Did a incremental file backup using All saveset.
6. Did a incremental backup of the SQL Server.
It works! The incremental backup was not promoted to full and the transactions logs was truncated. And the file backup is using VSS.
Just to be on the safe side I restore a copy of one of the databases, just to make sure it worked.
I have tried this on three MSSQL boxes, an all works.
ble1
4 Operator
•
14.3K Posts
0
March 25th, 2009 05:00
SFBing
12 Posts
0
March 27th, 2009 12:00
Be aware of a possible danger point in this approach.
Microsoft sets up the SQL Writers (or any application writers) as they do so that a file system save will skip any SQL files. If the SQL Writer is disabled, then when the file system backup of a disk that contains databases or logs is invoked, those files will get saved at snapshot time, but without having been quiesced and made consistent by the writer. When the file system saveset is recovered, the database will have errors, which SQL may or may not be able to repair. Further, after that happens, the logfile stream will have been interrupted, so that a subsequent NMSQL recovery would not be able to roll forward beyond the recovery point: you would be limited to a point-in-time recovery to the newest NMSQL saveset.
Thanks,
_Scott