Later this year, a new book by author Grant Fritchey, called Learn SQL Server in a Month of Lunches, will be released by Manning Publications. Below is a preliminary excerpt from the book on “Creating a Full Backup”. The book is geared toward readers new to SQL Server.

Creating a Full Backup

The full backup is a complete copy of the database. It copies everything. Not just all the data, but all the tables and all the indexes and all the stored procedures; everything that makes up the database gets copied. That’s what makes this so important for protection in the event of some type of catastrophic loss. As with most everything else in SQL Server, you can perform a full backup using either the SQL Server Management Studio (SSMS) graphical user interface (GUI), or you can use Transact SQL (TSQL) to make the backups happen. Let’s start with the GUI.

Using SSMS to create a full backup

Connect to the server and browse through the Object Explorer window to the list of databases on the server. Right-click on the database you want to back up and then right-click on it to open the context menu. Scroll down the menu and select Tasks and then Backup… to open the Back Up Database window that you can see in figure 1.

Figure 1: The Backup Database window set up to create a full backup of the sample MonthOfLunches database.

The left-hand side of the window has pages you can select—General and Options. The right side displays the details of the selected page. The default General page is on display at the moment. You can pick the database you want to back up and you can select the type of backup you want to run. The default is a full backup. Just below the Source section is the Backup set section. Here, you can provide information about the backup you’re about to create. The name of the backup has already been defined: MonthOfLunches-Full Database Backup.

You can modify this as you like. You also get a Description field that you can use to provide information about the backup that will help you later when you want to know what a backup represents. You can also set the backup to expire after a certain number of days or on a specific date. This is only useful if you’re keeping multiple database backups in a single file, referred to as a backup set. We’ll talk a little more about this when we switch to the Options page. Finally, at the bottom of the screen, you get to select the Destination of the backup. This can be to a file or to a tape backup system. The file is determined automatically based on the settings on the server. You can add more files or modify the location of the backup by removing the existing file and then adding another. Just remember—if you add multiple files, they will all be used to create the backup. This is known as a mirrored backup. It’s useful for very large systems but not so much for smaller systems, defined as those smaller than 500 GB. With just the defaults checked, you could click the OK button and it will run a backup.

Clicking on the Options page on the left side of the window will open that page as shown in figure 2.

Figure 2: The Back Up Database window for the sample MonthOfLunches database showing the default options.

At the top of the Options page, you will find a section titled Overwrite Media. This is the one tricky part of taking a full backup of a database. By default, the GUI is set to append the backups to a single file. This means it stacks them there, which is fine if that’s what you want it to do. You can even make it so that the backups within the file are automatically removed (they’ll actually just be overwritten) by setting the number of days or providing a date for the backup to expire, which you can do on the previous page. I find this a very poor way to manage backups. When you go to restore the database, you can’t simply select a file and use that to restore. You must open that file and examine the backups within it to determine which is the one you’re looking for.

Instead, I change the default options to Overwrite all existing backup sets so that each backup is an individual file. I can name them separately as well as provide them with descriptions so that I know everything about the backup just from looking at the file—no extra steps required. I would recommend using this approach.

Below the Overwrite Media section is the Reliability section. Here, you have a few options that are worth looking into. First, you can run a check against the backup to verify that it wrote correctly to the file by checking Verify backup when finished. You can also ensure that the writes are checked as they occur by enabling Perform checksum before writing to media. I strongly recommend using both of these options.

Finally, at the bottom of the page, you decide whether or not to use backup compression with the backup. This is a way of making the backups smaller so that they take up less room. It also can make the backup process a little bit faster but puts a bigger load on the CPU. I’d recommend using the setting if it’s available with the version of SQL Server 2012 that you have installed.

With all that set, you can now click on the OK button and a backup of your database will be created in the file location you specified.

You now know how to back up a database using SSMS. Let’s try it with TSQL.

Backing up with TSQL

The GUI makes things easy and TSQL makes things powerful. You already have a good understanding of how backups work, so we really only need to focus on the syntax of the BACKUP command. You can create a backup file or a backup set. You can also just use the database name as the backup file name. It’s not an uncommon practice. However, you can do more. What if you wanted to make the date part of the name of the backup file so that you could always look at a backup and know when it was taken? Further, you can add the name of the server to the file name so that you know which server it was from too. Here’s a TSQL command to back up the database and provide those customizations at the same time:

DECLARE @FileName NVARCHAR(100) ;
SET @FileName = ‘c:\data\MonthOfLunches_’ + CONVERT(NVARCHAR(8), GETDATE(), 112)
+ ‘_’ + @@SERVICENAME + ‘.bak’ ;
BACKUP DATABASE MonthOfLunches
TO DISK = @FileName
WITH INIT,COMPRESSION,CHECKSUM ;

The first two commands are just building the special name and location that we’re going to use to create our backup. The variable is declared and then a string is built to combine the database name with the current date and time and the name of your SQL Server instance, @@SERVICENAME. We give it a file extension .bak but it really could be anything you like.

The real work is in the next line. The basic command is to run a BACKUP of the DATABASE and then you have to provide the name of the database. TO provides the location where the backup will be, and, in this case, we’re using the DISK and then providing the path and file name created in the @FileName variable. Finally, the WITH statement sets all the options in order to provide a new file instead of a backup set, INIT, smaller backup files, COMPRESSION, and validation of the file as it gets written to disk, CHECKSUM.

Summary

You’ve learned all there is to it to start creating backups of all the databases in your system. Because the data in your databases changes a lot during the day depending on the system and because full backups take long, it’s not something you’re going to run more than once a day.

Book excerpt reprinted with permission from the author and publisher.