Problem
Sometimes backup software that is used to backup the Windows servers on which SQL Server is running will trigger a SQL backup automatically. Although you may have setup a neat SQL backup schedule in SQL Server Agent with Full, Differential and transaction log backups, consequences are that whenever this Server backup software kicks in and triggers another full backup of your databases, your backup files may be the wrong ones in case you ever need to do a restore with your Full, differential and transaction log backups, because the order in which they are created by you is broken by the unwanted backup in the background.
You do not see the unwanted backups in the SQL server Agent log, but you will find them back in the standard SQL server logs. The SQL server log typically show lines like this:
- Database backed up. Database: master, creation date(time): 2015/02/19(16:52:39), pages dumped: 1356, first LSN: 388:415:117, last LSN: 388:465:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{9603DF6C-C7E9-4A19-BAAF-C827EFE22023}7'}).
- I/O was resumed on database master. No user action is required.
- 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.
The same messages are repeated for all the other system and user databases on that particular server.
Cause
The SQL server service called ‘SQL Server VSS Writer’ is used by the Windows backup software to trigger the SQL server backup. From SQL BOL:
When running, Database Engine locks and has exclusive access to the data files. When the SQL Writer Service is not running, backup programs running in Windows do not have access to the data files, and backups must be performed using SQL Server backup.
-
‘Use the SQL Writer Service to permit Windows backup programs to copy SQL Server data files while SQL Server is running.’
Solution
In the Services management console: Stop and disable the ‘SQL Server VSS Writer’ service.
No comments:
Post a Comment