Search

SQL Server 2008 R2, Windows 7 x64, Windows Backup, VSS, SQL Writer Service by Olaf Pietsch DETECON

Closed
as By Design Help for as By Design

7
0
Sign in
 to vote
Type: Bug
ID: 573067
Opened: 7/4/2010 3:38:16 AM
Access Restriction: Public
1
Workaround(s)
0
User(s) can reproduce this bug
On windows 2008 R2 we use windows backup to backup the SQL Server database files while SQL Service is running. (The SQL Server database files were not excluded.) In the error log of SQL Server we found entries like:
... I/O is frozen on database master. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
... I/O was resumed on database master. No user action is required.

I understand these entries showing that the ntbackup with VSS-Service, SQL Writer Service and SQL Server works correctly. The database files are backup in a consistent state.

Doing the same on Windows 7 (with running SQL Server) we could _not_ find any similar entry in the SQL Server error log mentioned above after backup has finished.

We are now unsure that the database files are backup in a consistent state with Windows 7 backup while SQL Service is running.
Details (expand)
Product Language
English

Version

SQL Server 2008 R2 - Developer Edition

Category

SQL Engine

Operating System

Windows 7 Ultimate
Operating System Language
English
Steps to Reproduce
Make a backup with Windows 7 Backup while SQL Service is running and check errlog for a. m. entries.
Actual Results
We see this behavior on several installations SQL Server 2008 R2 on windows 7 x64.

To be sure that the SQL Writer Service is running before SQL Service starts, I rebooted the machine, checks if SQL Writer service is running and I restarted SQL Service, made a backup and had the same result: no entries.

I installed the developer edition on the server OS (Windows 2008 R2). I made a backup with windows backup. The o. m. entries are found in the error log. It seems ok.
Expected Results
The o. m. entries in the error log of SQL Server using windows backup while SQL Service is running should be the available on each operating system, windows 7 or windows server.

If this behavior is by design, it should be explaind in BOL, that there is a difference in the error log entries of SQL Server when windows backup is used between windows as a client OS and windows server OS.

Platform

X64
File Attachments
0 attachments
Sign in to post a comment.
Posted by Stefan Falk on 7/29/2010 at 9:33 AM
Hello everybody,

This issue has an impact for me as a software developer running SQL Server Developer Edition locally. Windows Backup definitely should include other VSS writers, or at least have an opt-in choice available to do so. What sense do VSS writers make if, without our knowing until now, they aren't used at all?

The same holds true for Windows 7 Hyper-V VMs with SQL databases, if just the VMs are backed up (using Data Protection Manager, for example), and consistency entirely depends on the VSS writers inside the VMs. Not a quite usual scenario, but still possible I guess.

Btw, what was the reason for the backup team to change that in Windows 7? Are there so bad VSS writers out there?

Best Regards,
Stefan Falk
Posted by Ralf Dietrich - datafino GmbH on 7/24/2010 at 4:47 PM
As you're writing that win 7 nativly has no option to include the requested feature - it might move to the windows client team to put this feature back to us as soon the next SP ships.
It's usually not easy to have consistend backups for clients but with a two step approach it becomes more complex and resides outside te scope from most business users. (please be aware that currently the SQL Express Edition does not support SQL Agent!)
Maybe they will include this ability for the sql writer feature in business editions of windows 7 only.
Posted by Microsoft on 7/13/2010 at 4:41 PM
As confirmed to me by the Windows backup team, Windows 7 Backup explicitly excludes any Writer which is not itself part of Windows. This means that the Windows backup app on Win7 excludes the SQLWriter, which is why you don't see any log entries from it.
On the Server backup app, they do pull in more Writers sucn as SQL Server so you see the log entries as expected.

The recommendation would be to use TSQL to back up the databases on Win7. This has the advantage that TSQL backup only includes the allocated extents within the database, so backups will be smaller.
If you need to archive, you could then use Windows Backup to save the TSQL backup files themselves.
Posted by Microsoft on 7/9/2010 at 9:51 AM
Olaf,
I am contacting the Windows Backup team.
I recall a conversation with them about Windows Backup specifically excluding any writer which was not part of the System backup.
This would explain the lack of log entries on Win7, as the SQL Writer would not be invoked on that platform.

I need to both verify my memory and get details.
I will keep you posted when I hear back.

Kevin Farlee
SQL Server Storage Engine PM
Sign in to post a workaround.
Posted by ThomasGlörfeld on 9/16/2010 at 11:31 AM
Microsoft published a hotfix for Windows 7 addressing this problem: <a href="http://support.microsoft.com/kb/2288055/en">KB2288055</a>.
© 2012 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement