Appending Backup Sets

Administering SQL Server

Administering SQL Server

Appending Backup Sets

Backups performed at different times from the same or different databases can be stored on the same media. Additionally, data other than Microsoft® SQL Server™ data can be stored on the same media, such as Microsoft Windows NT® 4.0 file backups. By appending a new backup set to existing media, the previous contents of the media remain intact, and the new backup is written after the end of the last backup on the media.

By default, SQL Server always appends new backups to media. Appending can occur only at the end of the media. For example, if a media contains five backup sets, it is not possible to skip the first three backup sets to overwrite the fourth backup set with a new backup set.

If you use BACKUP WITH NOREWIND for a tape backup, the tape will be left open at the end of the operation. This allows you to append further backups to the tape without rewinding the tape and then scanning forward again to find the last backup set. A list of currently open tapes can be found by querying the sysopentapes table in the master database.

Windows NT 4.0 and Microsoft Windows® 2000 backups and SQL Server backups are not interoperable. Though media can be shared between the two, a SQL Server backup cannot be used to backup Windows NT 4.0 data. You can use NTBackup to backup database files if an instance of SQL Server is not running. Do not rely on file-level backups using NTBackup if an instance of SQL Server is running.

To append a new backup to existing media