SQL Writer Service
The SQL Writer Service provides added functionality for backup and restore of SQL Server 2005 through the Volume Shadow Copy Service framework.
This SQL Writer Service is automatically installed but not enabled by default. It must be explicitly enabled to run on the server machine and must be running at the time that the Volume Shadow Copy Service (VSS) application requests a backup or restore. Use the Microsoft Windows Services applet to configure the service. The SQL Writer Service installs on all operating systems but is only needed on server systems. For Microsoft Windows XP, use the MSDE Writer.
SQL Server 2005 Service Pack 2 configures SQL Writer to start automatically.
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.
Volume Shadow Copy Service
The VSS is a set of COM APIs that implements a framework to allow volume backups to be performed while applications on a system continue to write to the volumes. The VSS provides a consistent interface that allows coordination between user applications that update data on disk (writers) and those that back up applications (requestors).
The VSS captures and copies stable images for backup on running systems, particularly servers, without unduly degrading the performance and stability of the services they provide. For more information on the VSS, see your Windows documentation.
Virtual Backup Device Interface (VDI)
SQL Server provides an API called Virtual Backup Device Interface (VDI) that enables independent software vendors to integrate SQL Server into their products for providing support for backup and restore operations. These APIs are engineered to provide maximum reliability and performance, and support the full range of SQL Server backup and restore functionality, including the full range of hot and snapshot backup capabilities.
A VSS writer (MSDE Writer) shipped with the VSS framework in Microsoft Windows XP and Microsoft Windows Server 2003. This writer coordinates with SQL Server 2000 and earlier versions to help in backup operations. 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.
The SQL Writer service must run under the Local System account.
SQL Writer supports:
Full database backup and restore including full-text catalogs
When SQL Writer service is used to restore a backup, if a full-text catalog is associated with the database, restoring with recovery requires either that SQL Server be started before the restore operation or that the Microsoft Full-Text Engine for SQL (MSFTESQL) service also be stopped. For more information, see Understanding How Restore and Recovery of Backups Work in SQL Server.
Differential backup and restore
Restore with move
Auto-recovery of database snapshot
SQL Writer does not support:
- Log backups
- File and filegroup backup
- Page restore
5 December 2005