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
Date
Time spid52 Database Northwind: IO is frozen for snapshot
Date
Time spid53 Database pubs:
IO is frozen for snapshot
Date
Time spid55 Database model: IO is frozen for snapshot
Date
Time spid51 Database
master: IO is frozen for snapshot
Date
Time spid51 Database master: IO is thawed
Date
Time spid52 Database
Northwind: IO is thawed
Date
Time spid53 Database pubs: IO is thawed
Date
Time spid54 Database msdb:
IO is thawed
Date
Time spid55
Database model: IO is thawed
Date
Time 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.