From the course: Microsoft SQL Server 2022 Essential Training

Create a full backup of the database - SQL Server Tutorial

From the course: Microsoft SQL Server 2022 Essential Training

Start my 1-month free trial Buy for my team

Create a full backup of the database

“

- It should be no surprise that backing up your database is an exceptionally good idea. You never know when disaster is about to strike and I'm not just talking about earthquakes and fires or a hardware failure. Eventually there will come a day when you do something silly like casually forgetting a where clause in a delete statement and you wipe out an entire data table. There is no undo option when it comes to SQL Server, so you need to be ready for when that day comes. To make a backup, just right click on your database in the Object Explorer panel then point to tasks and choose backup. That'll open up the backup database dialogue. Now, the first option at the very top is to choose the database that you want to back up so you actually didn't need to right click the database over here in the Object Explorer. You can simply select the correct one from this dropdown. Let's create a backup of the Red 30 Tech database. The recovery model that's displayed depends on the properties of the database. In this case, the full recovery model means that the database backups will include full transaction logs in addition to all of the tabular data. Next, we have the backup type. This also says full. This time full means that everything from the database will be backed up. The other options are differential, which only backs up records that have changed since the last full backup. Or we could back up just the transaction log. I'll leave mine set to full. Next, let's look at the backup component. Our option is either the full database or if you're using multiple file groups you can choose specific files to back up. Our database is only using the primary file group so this option really doesn't apply. Next, we can specify the destination where we want to save the backup file too. Options here are to either use a disc drive or we can choose a URL. If you choose URL and then press the add button you'll be prompted for an Azure storage container which is a component of Microsoft's cloud platform that integrates with SQL Server. You can even create a new container in the cloud here by signing into your Azure account. I'm going to press cancel here and just save our backup file to our hard drive. So I'll choose the disc option. That'll display the default location in the box below. You can press the add button now to add in additional locations, but it's important to note that this isn't an option for backup redundancy. If you were to add multiple locations here your backups would be split with a portion of the data going into one location and a portion going into the other. For large databases, this can speed up the process of saving a backup, but in order to restore you'll need both files as neither will contain everything. For most users, you'll want to stick with a single backup location here and rely on other methods for creating redundant copies of your backup if you need them. So that's the minimum amount of information that you need to provide. There are additional options in the other two pages that you could access on the left. Let's take a look at media options first. Here you have some options that ask about media sets. SQL Server keeps a record of backup and restore activity and it groups all of that history into this concept of a backup set. Here what's happening is that you can decide whether you want to continue contributing to the current history or if you want to wipe out that history and start again with a new backup set. One option on this screen that's probably a good idea to turn on is this checkbox down here that says verify backup when finished. That will just make sure that it wrote to the target location without any issues before saying that the backup was successful. And finally, we could take a look at the backup options page. Here we can set in expiration for the backup set, in this case, after zero days means that it won't expire. We can also enable encryption on the database backup if we want. When all of your options are dialed into your liking press the okay button at the bottom of your screen and SQL Server will go through the process of backing up your database. Once you get the success message, you're all set. We now have a backup of the database stored on our hard drive. Now, there's one other option that we have with the backup window, so let's go back into it real quick. I'll right click on the Red 30 Tech database point to tasks and choose backup again. At the very top we have the option to view the script of our backup. I'll choose the option to script action to a new query window. Then I'll press cancel to dismiss the backup dialogue window and we can see our script right here. This will allow you to review the exact transact SQL code that creates your backup files and either make edits here or incorporate this code into a larger maintenance script that you may want to run regularly to ensure that you always have a recent backup of your database.

Contents