Before you can perform a database or transaction log backup, you must specify (or create) backup devices. SQL Server Management Studio allows you to create disk devices and tape devices in a similar manner. In both cases, expand the server, expand Server Objects, right-click Backup Devices, and choose New Backup Device. In the Backup Device dialog box (see Figure 16-1), enter the name of either the disk device (if you clicked File) or the tape device (if you clicked Tape). In the former case, you can click the … button on the right side of the field to display existing backup device locations. In the latter case, if Tape cannot be activated, then no tape devices exist on the local computer.
After you specify backup devices, you can do a database backup. Expand the server, expand Databases, right-click the database, and choose Tasks | Back Up. The Back Up Database dialog box appears (see Figure 16-2). On the General page of the dialog box, choose the backup type in the Backup Type drop-down list (Full, Differential, or Transaction Log), enter the backup set name in the Name field, and optionally enter a description of this set in the Description field. In the same dialog box, you can choose an expiration date for the backup.
In the Destination frame, select an existing device by clicking Add. (The Remove button allows you to remove one or more backup devices from the list of devices to be used.)
On the Options page (see Figure 16-3), to append to an existing backup on the selected device, click the Append to the Existing Backup Set radio button. Choosing the Overwrite All Existing Backup Sets radio button in the same frame overwrites any existing backups on the selected backup device.
For verification of the database backup, click Verify Backup when Finished in the Reliability frame. On the Options page, you can also choose to back up to a new media set by clicking the Back Up to a New Media Set, and Erase All Existing Backup Sets radio button and then entering the media set name and description.
For creation and verification of a differential database backup or transaction log backup, follow the same steps, but choose the corresponding backup type in the Backup Type field on the General page.
After you have chosen all your options, click OK. The database or the transaction log is then backed up. You can display the name, physical location, and the type of the backup devices by selecting the server, expanding the Server Objects folder, expanding the Backup Devices folder, and then selecting the particular file.
Scheduling Backups with Management Studio
A well-planned timetable for the scheduling of backup operations will help you avoid system shortages when users are working. SQL Server Management Studio supports this planning by offering an easy-to-use graphical interface for scheduling backups. Scheduling backups using SQL Server Management Studio is explained in detail in the following chapter.