I think what happens here is that due to VSS backup SQL believes there has been backup of SQL as well. Next time SQL module runs it believes 3rd party did a backup so it promotes this into full.
This is known - I wonder what your support was talking about There is LGTsc too and I had the case like that too for one customer.
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
Thanks for a quick and detailed answer. It got me in the right direction.
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.
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.
ble1
4 Operator
•
14.4K 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.4K 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