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.

Backup compression was introduced in SQL Server 2008 Enterprise. Beginning in SQL Server 2008 R2, backup compression is supported by SQL Server 2008 R2 Standard and all higher editions. Every edition of SQL Server 2008 and later can restore a compressed backup.  However, compressed and uncompressed backups cannot occur together in a media set. 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