Export (0) Print
Expand All

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 (Transact-SQL).

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.

SQL Server 2008 Enterprise and later supports compressing backups. Compressed and uncompressed backups cannot occur together in a media set. Any edition of SQL Server 2008 or later versions can read compressed backups. For more information, see Backup Compression (SQL Server)

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.

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.

To append a new backup to existing media using SQL Server Management Objects (SMO)

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft