Microsoft KB Archive/903643

= The Ntbackup.exe operation may break the differential backup chain of a SQL Server database and may invalidate the differential backups when you perform a snapshot backup of the database =

Article ID: 903643

Article Last Modified on 11/2/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Personal Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Workgroup Edition
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft Windows Server 2003, Datacenter Edition (32-bit x86)
 * Microsoft Windows Server 2003, Enterprise Edition (32-bit x86)
 * Microsoft Windows Server 2003, Enterprise Edition for Itanium-based Systems
 * Microsoft Windows Server 2003, Standard Edition (32-bit x86)
 * Microsoft Windows Server 2003, Web Edition

-





INTRODUCTION
The Ntbackup.exe utility uses the Virtual Device Interface (VDI) to perform a snapshot backup of a Microsoft SQL Server 2000 database. When you use this utility, the operation may break the differential backup chain of the SQL Server database and invalidate the differential backups.



MORE INFORMATION
System administrators can use the Ntbackup.exe utility in Windows Server 2003 to back up SQL Server databases when SQL Server is running. When the Ntbackup.exe utility starts the backup process, the following behavior occurs:
 * SQL Server opens database files in exclusive mode.
 * SQL Server prevents other processes from reading the database files even if the files are being used.

In Windows Server 2003, changes were made to the Ntbackup.exe utility. These changes let you perform a VDI snapshot backup of SQL Server databases. After you successfully run the Ntbackup.exe utility, the following message is written to the SQL Server error log:

2005-03-07 12:53:14.17 spid54 Database msdb: IO is frozen for snapshot

spid52 Database Northwind: IO is frozen for snapshot

spid53 Database pubs: IO is frozen for snapshot

spid55 Database model: IO is frozen for snapshot

spid51 Database master: IO is frozen for snapshot

spid51 Database master: IO is thawed

spid52 Database Northwind: IO is thawed

spid53 Database pubs: IO is thawed

spid54 Database msdb: IO is thawed

spid55 Database model: IO is thawed

backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52), pages dumped: 1, first LSN: 5:268:1, last LSN: 5:272:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{3805AE94-9393-4E93-9DCD-292771DA7239}2'}).

2005-03-07 12:53:16.72 backup Database backed up: Database: Northwind, creation date(time): 2000/08/06(01:41:00), pages dumped: 1, first LSN: 25:156:1, last LSN: 25:160:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{3805AE94-9393-4E93-9DCD-292771DA7239}5'}).

2005-03-07 12:53:16.72 backup Database backed up: Database: pubs, creation date(time): 2000/08/06(01:40:58), pages dumped: 1, first LSN: 5:192:1, last LSN: 5:196:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{3805AE94-9393-4E93-9DCD-292771DA7239}4'}).

2005-03-07 12:53:16.89 backup Database backed up: Database: master, creation date(time): 2005/03/07(12:47:00), pages dumped: 1, first LSN: 180:42:1, last LSN: 180:46:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{3805AE94-9393-4E93-9DCD-292771DA7239}1'}).

2005-03-07 12:53:16.89 backup Database backed up: Database: msdb, creation date(time): 2000/08/06(01:40:56), pages dumped: 1, first LSN: 92:370:1, last LSN: 92:374:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{3805AE94-9393-4E93-9DCD-292771DA7239}3'}).

Before hotfix 828481, the Ntbackup.exe utility did not back up SQL Server databases if any user database used the FULL recovery database model. The Ntbackup.exe utility only worked when all user databases used the SIMPLE recovery database model.

Hotfix 828481 modified the Ntbackup.exe utility to allow for the SQL Server database backup process to also use other recovery models.

