Appending to Existing Backup Sets
Backups performed at different times from the same or different databases can be stored on the same media. By appending another 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 volume 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. You can find the list of open tape drives in the sys.dm_io_backup_tapes dynamic management view; for more information, see sys.dm_io_backup_tapes.
Microsoft Windows backups and SQL Server backups can share the same media, but they are not interoperable. SQL Server backup cannot back up Windows data.
To append a new backup to existing media using Transact-SQL
To append a backup to the existing backup sets of a media set, use the NOINIT option of the BACKUP statement.
- BACKUP (Transact-SQL)
- How to: Create a Full Database Backup (Transact-SQL)
- How to: Create a Differential Database Backup (Transact-SQL)
- How to: Create a Transaction Log Backup (Transact-SQL)
To append a new backup to existing media using SQL Server Management Studio
To append a backup to the previous backup set of a media set, select Append to the existing backup set on the Options page of the Back Up Database dialog box.
- How to: Back Up a Database (SQL Server Management Studio)
- How to: Back Up a Transaction Log (SQL Server Management Studio)
- How to: Create a Differential Database Backup (SQL Server Management Studio)