Monday, April 22, 2013

Appending to Existing Backup Set in SQL Server

When you are taking a backup of SQL Server database, you can append the backup to an existing backup set, which is the default option selected. This can be done by selecting an existing backup file (if you are using a disk backup) as the destination. Appending to an existing backup set is also possible with tape backups. Once you select the same backup file, and choose to append to an existing backup set, the new backup will be appended to the end of the previous backup.

When it comes to restoring, if you select a backup file, it will select, by default, the last backup set in the backup media. But how can we select a different backup set to be restored? Once you select the backup media for restoring, the last backup set will be selected. You can see that in the "Backup sets to restore" list, the name of the last taken backup set is shown. However, you can select a previous backup set by clicking  "Timeline" button in the "Destination" section, right above the backup set list. There you get a timeline of backup sets available in the selected media, and provides the option to select which backup set should be restored. You can simply click on the backup set, or select the date/time of the backup set based on which the correct backup set will be selected from the timeline. Once the backup set is selected, SQL Server will restore it instead of the latest.

No comments: