Join Martin Guidry for an in-depth discussion in this video Performing a basic backup using the GUI, part of Microsoft SQL Server 2014: Backup and Recovery.
- [Voiceover] In this section, we're going to talk about performing a basic backup using the graphical interface. I'm going to pull up SQL Server Management Studio. I'll connect to a local instance of SQL Server. I'll expand the tree for Databases. I'll highlight one of the user databases. Then I'll right click and off of the task menu, I see the option to backup. This will pull up the dialogue box for backup. Most of the things here I can go ahead and accept the default.
That is the correct database I want to backup. It's telling me it's in the simple recovery model and I do not have the option to change that. I would like to do a full backup and that's what was selected by default. I would like to backup to disk. The other option in this drop-down is URL and if I had a tape drive, then the tape drive would show up here. Because I selected disk, it's asking me for a file name. I'm going to go ahead and accept the default. At the bottom, I'll click OK.
In the lower left, we'll see a progress bar go, and it'll show a percent complete. It's up to 100% and I got a message saying the backup completed successfully. That is all good news. I'll click OK here. Let's go ahead and look for that backup. I told it to put it on the F drive, in a folder called SQL Backup. Yes, I have a file called Adventure.bak. Exactly like I expected. We'll pull up that interface again and look at some of the other options.
Again, right click on the name of the database, go to task, and backup, and that pulls up the interface. There are three tabs, General, Media Options, and Backup Options. We've already looked at General. Let's look at Media Options. The default is to append to the existing backup set. I also have the option to overwrite all existing backup sets or create a new media set.
You'll have to give it a name and a description. We also have options. Each of these can be turned on or turned off individually. Verify when finished, perform a check before writing to the media, and continue on error. The first two I typically like to do. Go ahead and verify and perform the checksum. Continue on error I do not like. If there's an error, I want to go ahead and stop. Note that verify backup when finished can significantly increase the amount of time to perform the backup.
The test that's performed after the backup takes almost as much time as the backup itself, so these two options together can double the amount of time it takes to perform a backup, which can be a concern in some situations. If I was backing up the transaction log, I would have to option to truncate the log, and if I was backing up to a tape drive, I would have some different options, such as rewinding the tape. The next tab I select over here to the left, Backup Options, the first is a name. A name was provided for me by default and I don't see much reason to change that.
You can provide an optional description. This does not affect functionality. It's just something for a human being to know why this backup was taken and maybe a brief description of what was going on. For example, if you did a backup right before you applied a service pack, you might make a note of that in this description field. Backups that will expire, typically we say zero days, meaning it will not expire. We do have the option to set a date on which the backup will expire. Once a backup is expired on a tape, it will be overwritten on that tape automatically.
The next two options, compression and encryption, are only available on some versions of SQL Server. I am using the Enterprise edition, so I do have the options for both compression and encryption. If you're using a different version of SQL Server, such as Standard edition or Express edition, you may not get the option to compress or encrypt. If you do have the option to compress, I almost always do, in fact, compress. It tends to speed up the amount of time of both the backup and the restore.
That's because the biggest bottleneck in backup and restore is almost always the disk or the tape. Whatever media we're writing to. If we compress, that means we're writing or reading a lower amount of information from the media, so it should run faster, if we're writing less information to the media. We also have the option to encrypt the backup. We have different encryption algorithms and then we'll need to specify a key. I do not currently have any keys created and we will talk about that a bit later.
These are the various options in the graphical interface. Again, our three pages are General, Media Options, and Backup Options, and in most situations, you're probably going to accept the defaults for most of the fields. Be sure and make a note of the file name you are backing up to, so that you know where your file is.
- Types of backups
- Media sets and backups
- Performing a basic backup using the GUI or command line
- Performing a basic restore using the GUI or command line
- Backing up and restoring logs
- Doing partial backup and restores
- Automating backups
- Using encryption and compression