Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Once we've done at least one full backup, we can actually continue to do the next ones by doing differential ones, although every now and then we'll do a full backup again. All we do with this is go in and just change our backup type to Differential, and I can just click OK. Now that's not very exciting, so let me show you a couple of other options that you have there. Once again just going back into the database, Tasks and Backup. To get a bit of information about how many times we have done a full backup, how many times you've done a differential backup, we can select the destination path and come down here to the Contents button.
And that will allow us to kind of step through or different backup sets, which ones were full, which ones were differential, and how long we've been keeping them around for. This whole collection, this whole suite of full and differential backups, are in order and they are known as a media-set. SQL Server likes to understand the history of how you've done your full and your differential backups. It makes a lot of use of that. That can be impactful if you want to change a few other options. So let's say I drop into the second page here and I can see that my default is I am backing up to the existing media-set.
And I start to look at some of the other information like Verify backup and Perform checksum on media. And then I look at this guy here. Compression, Set backup compression. This is a reasonably new feature with SQL Server. If you have large databases it's a good thing to have, because you can't compress the backup files. With the choice of whether we do or not is based by default on the server setting. What does the server want to do, and I'll show you how to change that in a minute. But let's say I've looked at this and think, oh well I can apparently override this, great.
While I'll decide that I'm going to compress my backup. So I'll click OK and it says "No, you won't." That's because when you've got a media-set of full and differential backups they either have to be all uncompressed or all compressed, you can have a bit of both. So my options here, if I wanted to compress the backup is that I have to click this guy here to backup to a new media-set and erase all the old ones. I don't want to do that, so I'm actually going to leave it at the default and just run through it there.
One last thing that I could do, if I don't want to come into SQL Server Management Studio and perhaps I just want to script a job to occur, we can, once we've set this up, let's say I want to get ready to do differential one, I'll check my options and they look all okay, I can come out to the top of the window and click the script button, which gives me a few options. Generate a script action to a new query window, script action default to clipboard, to job, what does this do? Well, it's quite simple. If I hit that, Cancel, I'm going back now here it's generated the SQL version of what we were just doing with SQL Server management studio.
This is the SQL command for doing a backup. We are backing up the database to this particular address and it's a differential backup. So if you want to start writing your own commands, you could always start off with that window and then just script it and make any changes that you wanted to do. But finally on that idea of setting the server default for our backup compression, if you want to, you can change this at the server level. I'm going to right-click the instance name itself, come down to properties, and you'll find that particular setting under the database settings here, where we've got the option here to compress backup or not.
Now I'm going to leave it unchecked as it was before, but that's where I could change it, if I was very much intending to do backup compression on everything.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 86660 Viewers
80 Video lessons · 135973 Viewers
59 Video lessons · 54707 Viewers
52 Video lessons · 68603 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.