We are now ready to start the full backup process. Open SQL Server Management Studio, connect to your server, expand the Databases node and then right-click on the DatabaseForFullBackups database, and navigate Tasks | Backup…, as shown in Figure 3-3.
Chapter 3: Full Database Backups
Figure 3-3: Back Up Database menu option.
This will start the backup wizard and bring up a dialog box titled Back Up Database – DatabaseForFullBackups, shown in Figure 3-4, with several configuration options that are available to the T-SQL BACKUP DATABASE command. Don't forget that all we're really doing here is using a graphical interface to build and run a T-SQL command.
Figure 3-4: Back Up Database wizard.
The General page comprises three major sections: Source, Backup set and Destination.
In the Source section, we specify the database to be backed up and what type of backup to perform. The Backup type drop-down list shows the types of backup that are available to your database. In our example we are only presented with two options, Full and Differential, since our database is in SIMPLE recovery model.
Chapter 3: Full Database Backups
You will also notice a check box with the label Copy-only backup. A copy-only full backup is one that does not affect the normal backup operations of a database and is used when a full backup is needed outside of a normal scheduled backup plan. When a normal full database backup is taken, SQL Server modifies some internal archive points in the database, to indicate that a new base file has been created, for use when restoring subse-quent differential backups. Copy-only full backups preserve these internal archive points and so cannot be used as a differential base. We're not concerned with copy-only backups at this point.
The Backup component section is where we specify either a database or file/filegroup backup. The latter option is only available for databases with more than one filegroup, so it is deactivated in this case. We will, however, talk more about this option when we get to Chapter 9, on file and filegroup backups.
In the Backup set section, there are name and description fields used to identify the backup set, which is simply the set of data that was chosen to be backed up. The infor-mation provided here will be used to tag the backup set created, and record its creation in the MSDB backup history tables.
There is also an option to set an expiration date on our backup set. When taking SQL Server backups, it is entirely possible to store multiple copies of a database backup in the same file or media. SQL Server will just append the next backup to the end of the backup file. This expiration date lets SQL Server know how long it should keep this backup set in that file before overwriting it. Most DBAs do not use this "multiple backups per file"
feature. There are only a few benefits, primarily a smaller number of files to manage, and many more drawbacks: larger backup files and single points of failure, to name only two.
For simplicity and manageability, throughout this book, we will only deal with backups that house a single backup per file.
The Destination section is where we specify the backup media and, in the case of disk, the location of the file on this disk. The Tape option button will be disabled unless a tape drive device is attached to the server.
As discussed in Chapter 1, even if you still use tape media, as many do, you will almost never back up directly to tape; instead you'll back up to disk and then transfer older backups to tape.
When using disk media to store backups, we are offered three buttons to the right of the file listing window, for adding and removing disk destinations, as well as looking at the different backup sets that are already stored in a particular file. The box will be pre-populated with a default file name and destination for the default SQL Server backup folder. We are not going to be using that folder (simply because we'll be storing our backups in separate folders, according to chapter) so go ahead and use the Remove button on that file to take it out of the list. Now, use the only available button, the Add…
button to bring up the Select Backup Destination window. Make sure you have the File name option selected, click the browse (…) button to bring up the Locate Database Files window. Locate the SQLBackups\Chapter3 directory that you created earlier, on the machine and then enter a name for the file, DatabaseForFullBackups_Full_Native_1.bak as shown in Figure 3-5.
Chapter 3: Full Database Backups
Once this has been configured, click OK to finalize the new file configuration and click OK again on the Select Backup Destination dialog box to bring you back to the Back Up Database page. Now that we are done with the General page of this wizard, let's take a look at the Options Page, shown in Figure 3-6.
The Overwrite media section is used in cases where a single file stores multiple backups and backup sets. We can set the new backup to append to the existing backup set or to overwrite the specifically named set that already exists in the file. We can also use this section to overwrite an existing backup set and start afresh. We'll use the option of Overwrite all existing backup sets since we are only storing one backup per file. This will make sure that, if we were to run the same command again in the event of an issue, we would wind up with just one backup set in the file.
The Reliability section provides various options that can be used to validate the backup, as follows:
• Verify backup when finished
Validates that the backup set is complete, after the backup operation has completed. It will make sure that each backup in the set is readable and ready for use.
• Perform checksum before writing to media
SQL Server performs a checksum operation on the backup data before writing it to the storage media. As discussed in Chapter 2, a checksum is a special function used to make sure that the data being written to the disk/tape matches what was pulled from the database or log file. This option makes sure your backup data is being written correctly, but might also slow down your backup operation.
• Continue on error
Instructs SQL Server to continue with all backup operations even after an error has been raised during the backup operation.
The Transaction log section offers two important configuration options for transaction log backups, and will be covered in Chapter 5.
The Tape Drive section of the configuration is only applicable if you are writing your backups directly to tape media. We have previously discussed why this is not the best way for backups to be taken in most circumstances, so we will not be using these options (and
Chapter 3: Full Database Backups
The final Compression configuration section deals with SQL Server native backup compression, which is an option that we'll ignore for now, but come back to later in the chapter.
Having reviewed all of the configuration options, go ahead and click OK at the bottom of the page to begin taking a full database backup of the DatabaseForFullBackups database. You will notice the progress section begin counting up in percentage. Once this reaches 100%, you should receive a dialog box notifying you that your backup has completed. Click OK on this notification and that should close both the dialog box and the Back Up Database wizard.