Overwriting Backup Sets
When overwriting backup sets on the media, the current backup overwrites the existing contents, which is then no longer available. Overwriting backup sets always overwrites all backup sets in a media set.
For tape headers, leaving the header in place can make sense. For disk backup media, only the files used by the backup devices specified in the backup operation are overwritten; other files on the disk are unaffected. When overwriting backups, any existing media header is preserved, and the new backup is created as the first backup on the backup device. If there is no existing media header, a valid media header with an associated media name and media description is written automatically. If the existing media header is invalid, the backup operation terminates. If the media is empty, the new media header is generated with the given MEDIANAME, MEDIAPASSWORD, and MEDIADESCRIPTION, if any.
Note
The MEDIAPASSWORD option will be removed in a future release.
Backup media is not overwritten if either of the following conditions is met:
- The existing backups on the media have not expired. (If SKIP is specified, expiration is not checked.)
The expiration date specifies the date that the backup expires and can be overwritten by another backup. You can specify the expiration date when a backup is created. By default, the expiration date is determined by the media retention option set with sp_configure. For more information, see sp_configure (Transact-SQL). - The media name, if provided, does not match the name on the backup media.
The media name is a descriptive name used for easy identification of the media.
If you are sure you want to overwrite the existing media (for example, if you know that the backups on the tape are no longer needed), you can explicitly skip these checks.
If the backup media is password protected by Microsoft Windows, Microsoft SQL Server does not write to the media. To overwrite media that is password protected, you need to reinitialize the media.
To overwrite existing backup sets using Transact-SQL
- BACKUP (Transact-SQL)
To overwrite all existing backup sets but preserve the existing media header, use the INIT option of the BACKUP statement. - 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 overwrite existing backup sets using SQL Server Management Studio
To overwrite backup sets but preserve the existing media header, select the Overwrite all existing backup sets option on the Options page of the Back Up Database dialog.