5 Ways to Make SQL Server Backups

Last time we discussed 5 backup types for SQL Server.  Now I want to introduce you to five ways to make backups in SQL Server. I can’t possibly explore all the options available with each of these six methods. There is a lot to cover for even a “simple” topic like backups.

Method 1: Using the SSMS GUI to Make a Back up

 

For those readers who like the SSMS GUI, there is good news.  SQL Server Management Studio offers a fairly straightforward method for backing up a database. Once you connect to the SQL Server instance containing the database you want to back up, left click on the “+” sign next to the Databases folder.  Then right click the name of the database you want to backup, choose Tasks, then choose the Backup option in the fly out menu.

That brings you to the General Backup Menu page in SSMS.  In this menu you can access a number of settings related to the backup that will be generated.

With the “Backup type” drop down you can control whether this will take a Full, Differential or Log backup.

The “Backup component” section can be adjusted to take either a backup of files and filegroups or the default selection of “Database.”

In the Destination section you can backup to the default of “Disk” or choose “URL” from the drop down to make a backup to Azure as the target. When the destination of Disk is left you will be supplied with the destination of the backup and a filename for the backup.  This directory will be the default backup directory selected during the SQL Server installation.  If this is not where you want the backup, simply click “Remove” then click “Add” to navigate to the location you want to use.  The “Add” menu does accept share paths.

The Media Options selection on the “Select a Page” allows you to choose options for things like whether you want to append this backup to the same media set or start over with a new one.

The media options seem to me to be from an earlier day when backups were written to physical tapes.  Those tapes then had to be rotated from time to time.  It’s not a best practice to append backups to the media set because you’re adding backup files into a single media set.  If something happens to the media set and it is unusable, then the backups are not accessible.

In the Reliability section, you should check the options for “Verify backup when finished” and “Perform checksum before writing to media.” Thes options will cause your backup to take longer to complete, but they do help with validating the integrity of the backup at the time it was written.

The Backup Options are in the “Select a page” menu has what I think is one very important feature to note.

On this page there is an option related to backup compression.  Back in older versions of SQL Server, like 2005 and 2008 this was an Enterprise Edition only feature.  As of SQL Server 2008R2 it is available in Standard Edition.  To make using this compression the default for all of your backups, simply run the code below on your SQL Server.  Then, when you get to this option in the SSMS GUI, just leave it set to “Use the default server setting.”  You’ll want the space savings that compression offers.  Why use more space on your separate storage for backups than is necessary? I mean, you are storing your backups somewhere other than on the SQL Server, right?!

EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Once you work through these meu options, simply click “Ok” and SQL Server will make the backup for you.  You can also click on the “Script” option at the top middle of the wizard to have SQL Server show you the T-SQL it is about to run.  You could save this as an example script to review later.

Method 2: Using T-SQL To Make a Backup

T-SQL is a tried and true method for backing up databases.  You have more options available when backing up with T-SQL versus the GUI.  Most of those options are going to be more advanced options. A very basic example of the backup command that produces a full backup is below. That is followed by examples for a differential backup and a log backup.

BACKUP DATABASE [MyDB] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup\MyDB_Full.bak'

BACKUP DATABASE [MyDB] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup\MyDB_Differntial.bak' WITH DIFFERENTIAL

BACKUP LOG [MyDB] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup\MyDB_log.trn'

Buffer Count and maxtransfersize are two options worth noting. You can experiment with these T-SQL options to take backups faster. The buffer count value conrols how many I/O buffers are used for processing the backup and the maxtransfersize controls how much data is moved at any one time.

Below I’ve provided three examples from my testing on my home lab PC. The initial buffercount and maxtransfersize data was captured by turning on trace flags 3605 and 3213 then looking in the error log after the first backup was taken.  After that I simply experimented with values.  Be aware that increasing the buffer count too much can cause an out of memory error.

As you can see, the initial throughput was 219.412 mb/sec and the elapsed time for that part was 39 seconds. This was using SQL Server defaults.

Increasing the buffer count to 8 increased the throughput to 258.653 mb/sec and the elapsed time dropped about 6 seconds. Combining the second change with a maxtransfersize of 4MB increased the throughput to 270.095 and pushed the time down another 1.4 seconds. I shaved 8 seconds off the backup time.  This was for a small database of about 14 GB.  For larger databases the increased throughput could represent a significant time savings.

BACKUP DATABASE [StackOverflow2010] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010_backup_2020_09_20_180836_9111962.bak' WITH NOFORMAT, INIT,  NAME = N'StackOverflow2010-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO
/*
buffercount = 4 and maxtransfersize = 1024kb
no options
Processed 1096456 pages for database 'StackOverflow2010', file 'StackOverflow2010' on file 1.
100 percent processed.
Processed 2 pages for database 'StackOverflow2010', file 'StackOverflow2010_log' on file 1.
BACKUP DATABASE successfully processed 1096458 pages in 39.041 seconds (219.412 MB/sec).
The backup set on file 1 is valid.
*/

BACKUP DATABASE [StackOverflow2010] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010_backup_2020_09_20_180836_9111962.bak' WITH NOFORMAT, INIT,  NAME = N'StackOverflow2010-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM, BUFFERCOUNT = 8
/*
Buffer Count = 8
Processed 1096456 pages for database 'StackOverflow2010', file 'StackOverflow2010' on file 1.
100 percent processed.
Processed 2 pages for database 'StackOverflow2010', file 'StackOverflow2010_log' on file 1.
BACKUP DATABASE successfully processed 1096458 pages in 33.118 seconds (258.653 MB/sec).
The backup set on file 1 is valid.
*/

BACKUP DATABASE [StackOverflow2010] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010_backup_2020_09_20_180836_9111962.bak' WITH NOFORMAT, INIT,  NAME = N'StackOverflow2010-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM, BUFFERCOUNT = 8, maxtransfersize = 4194304

/*
Buffercount = 8 and maxtransfersize = 4MB
Processed 1096456 pages for database 'StackOverflow2010', file 'StackOverflow2010' on file 1.
100 percent processed.
Processed 2 pages for database 'StackOverflow2010', file 'StackOverflow2010_log' on file 1.
BACKUP DATABASE successfully processed 1096458 pages in 31.715 seconds (270.095 MB/sec).
The backup set on file 1 is valid.
*/

 

Method 3: Using Powershell to Make a Backup

 

If you’re not using PowerShell with your SQL Servers, you should be.  If you’re not using the DBATools module with your SQL Servers, get it now. PowerShell can do fantastic, wonderful things and DBATools can do powerful, amazing things for you with respect to all things SQL Server. Below is a simple example of using the DBATools command Backup-DbaDatabase to make a full backup.  The command has a full range of options available., including backing up all the databases on a SQL Server when you don’t pass the -Database parameter. You should check into it right away.

Backup-DbaDatabase -SqlInstance MySQLServer -Database Stackoverflow2010 -path 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup'

 

Method 4: Using Maintenance Plans to Make a Backup

 

Because this post is already running long and there are a lot of other places that show how to use maintenance plans, I’m not going to step through how to do this here. You can go here and here to see how to use maintenance plans.  Also Jonathan Kehayias has a Pluralsight course on using them.

I am going to give you some quick thoughts on using maintenance plans though. First, Maintenance Plans are another GUI method to setting up backups.  In that respect, they are an easy point and click method to handle taking backups and handling backup retention, which we haven’t even talked about. Secondly, because of the nature of this method, which allows you to pick Backups as an option for a plan and then steps you through each part of a workflow wizard, Maintenance Plans can be a common approach for IT professionals coming from the SysAdmin side of things. No need to know or understand the options presented in the SSMS Backup wizard, for example.

Some downsides exist in backups inside maintenance plans prior to SQL Server 2016. Namely, the below options don’t exist.

– Perform checksum
– Continue on error
– Block size
– Max transfer size

Method 5: Using Ola Hallengren’s Maintenance Solution to Make A Backup

 

You can get help with Ola’s scripts here. Ola’s scripts are known and used world wide by DBAs.  They just work.  When you go to his page, you’ll download the file maintenancesolution.sql.  That file contains not only the backup routine but routines for CHECKDB and index maintenance.  You can download each components scripts individually as well. On his support page for the backup script, he explains all the available options and provides a laundry list of real world examples for how to set up the scripts for various scenarios.

Ola has options for the buffer count and maxtransfersize I demonstrated earlier.  He has options for using his scripts with AlwaysOn Availability Groups and he has options for backup retention. His scripts even work with third party vendor applications like LiteSpeed and Red Gate SQL Backup Pro.

What to Do Next:

  1. Review your current backup strategy and look for servers that aren’t being backed up at all.
  2. If you are a SysAdmin or an “Accidental DBA”, look at those links about setting up maintenance plans and get busy backing up your servers.
  3. If you didn’t know about DBATools, then stop what you’re doing and go to their web page.

5 Types of SQL Server Backups

One of the foundational responsibilities of a DBA is making sure that backups are available.  SQL Server has several foundational types of backups that can be made. Before I dive into that though, I want to ask a question that seems to have a fairly obvious answer.

Why take backups?

  1. You want to be able to recover from data corruption or mistakes made when people make changes directly to the data – like when they write and run a script in SSMS, outside of an application that’s using a set of tested stored procedures.
  2. You want to be able to refresh you non-prod environments so developers have data that looks like prod  to develop against.
  3. You want to have backups so you can test whether you can restore and how long it will take
  4. You want backups as they are often an essential part of the migration process from one SQL Server to another – usually newer SQL Server, or to Azure.

SQL Server Full Backups

What does a full backup accomplish?  A full backup, creates a backup of the entire database. It is all-inclusive, containing the entire database structure and all the data.  When a full backup starts, the Log Sequence Number is recorded and when the full backup completes, the Log Sequence Number is recorded.  This Log Sequence Number is the mechanism used by SQL Server to know what order INSERTS, UPDATES or DELETES occurred in.  As such, having the beginning and ending Log Sequence number recorded as part of the full backup allows for a transactionally consistent backup because the full backup is aware of the changes that took place while the backup was being made.  This allows for recovery of those transactions during the restore process. This full backup serves as the base backup, or starting point for all differential and log backups after it.  Each time a new full backup is taken, a new base backup point is created.

SQL Server Differential Backups

What does a differential backup do for you?  A differential backup works off the concept of a differential bitmap.  SQL Server is composed of eight kilobyte pages. These pages are collected in groups of eight called “Extents.” There is a bitmap that is used to mark any Extents that have experienced data changes since the last full backup. When a differential backup is executed, it is this bitmap that determines what data is backed up.

Based on the size and rate of change in a database, differential backups are often going to complete faster than a full backup.  Additionally, the differential backups will be smaller in size since only changed extents are backed up, rather than the entire database.

When the full recovery model is used, the power of the differential can be very important.  The differential backup reduces the number of log backups that have to be restored. For example, if there is a full backup taken on Sunday night, and transaction logs every 15 minutes after that, when a DBA needs to restore the database to Thursday at 5:53 AM, she needs to construct a restore process based on the Full backup and every transaction log since Sunday evening!  That’s a lot of transaction logs to have to include.  With a daily differential occurring at 5 AM, the DBA now only needs to restore the full backup from Sunday evening, the one differential that was created at 5 AM on Thursday, and only the transaction log backups that occurred after 5 AM Thursday. Being able to skip over using all those log backups is powerful.

This makes for much simpler script creation and readability, and can save a lot of time if the DBA is trying to write this script manually.  You should have an automated process to create the backup script, but some DBAs don’t have that set up yet. We’ll talk about automating the backups script creation  later in the series.

SQL Server File and FileGroup Backups

What are File and FileGroup backups? Well to start with, this is a more advanced topic than these other backup types. As such, it is less likely that you will need this type of backup. However, knowing what your options are is always a good idea.

File and FileGroup backups allow a more granular backup, and by extension, a more granular restore process. It does what it sounds like.  A file or filegroup backup backs up an individual file or a collection of files contained in a filegroup. This allows for restoring a small part of a database to fix a problem rather than having to restore the entire database. This option, however, does introduce complexity and, in my opinion, is really only useful for large databases, particularly in the range of 500 GB or more.

These file and filegroup backup types can be used when a larger database has been divided into multiple files.  For example, a large database containing sales information might be divided into various Sales files, perhaps by year, or even by month if there are a high volume of sales recorded in the database.  These files are then aggregated into FileGroups. See below for an example of creating files and filegroups in a database. This example was taken from the  MS docs page here.

USE master;
GO
-- Create the database with the default data
-- filegroup, filestream filegroup and a log file. Specify the
-- growth increment and the max size for the
-- primary data file.
CREATE DATABASE MyDB
ON PRIMARY
  ( NAME='MyDB_Primary',
    FILENAME=
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_Prm.mdf',
    SIZE=4MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),

--Here is where a separate file is created and assigned to a FileGroup, which is little more than a container.
FILEGROUP MyDB_FG1
  ( NAME = 'MyDB_FG1_Dat1',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_1.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),

--Here is where another separate file is created and assigned to a FileGroup.
  ( NAME = 'MyDB_FG1_Dat2',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_2.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB)
LOG ON
  ( NAME='MyDB_log',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB.ldf',
    SIZE=1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB);
GO
ALTER DATABASE MyDB 
  MODIFY FILEGROUP MyDB_FG1 DEFAULT;
GO

-- Create a table in the user-defined filegroup.
USE MyDB;
CREATE TABLE MyTable
  ( cola int PRIMARY KEY,
    colb char(8) )
ON MyDB_FG1;
GO

SQL Server Transaction Log Backups

What is a transaction log backup? A transaction log backup will take a backup of SQL Server’s transaction log so that data changes stored there can be recovered during the restore process.  Again the Log Sequence Number concept mentioned earlier is key.  Because transactions occur in a certain order, the log backups contain that order of transactions.  Transaction log backups must be restored in order.  If you try to restore a sequence of log backups in the wrong order, SQL Server will generate an error.

Having these log backups allows you to do what is called a point in time restore.  Having the option for a point in time restore can be critical for a business. Let’s suppose someone is making changes directly to one or more tables.  If a mistake is made and an unexpected data change occurs, point in time restore will allow you as the DBA to recover the data up to the moment just before the erroneous change was made.

SQL Server Copy Only Backups

What are Copy-only backups?  Copy only backups are backups that do not affect or track the Log Sequence Number information.  Why would you use this? Well, let’s suppose that you are already taking full, differential and log backups.  You are asked to provide a backup file of a database to another department or perhaps just a single developer who is working on a project. That Developer, hopefully, doesn’t have access to the backup directory for your backup processes.  Rather than you trying to copy a set of backups to a share or other location that the Developer does have access to, you could simply create a Copy Only full backup that is written to a shared location.  The Developer can then use that backup to restore locally to her machine, and you receive the benefit of not changing the base backup for your backup chain.

What to Do Next

  1. Examine your environment to see if you are leveraging the “Power of the Diff” by taking differential backups to help simplify your recovery process.
  2. Consider the size of your databases and determine if you have a situation that might benefit from having a database with multiple files and filegroups.
  3. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

Azure SQL Boot Camp Day 3 Takeaways

Hello reader!  Here we are with the much anticipated post for day 3 of the recent Azure SQL Boot Camp. Here are the previous posts for days one and two .

If you want to get started with Azure  SQL, the boot camp is a great way to do it.  You’ll get Azure experts like Anna Hoffman and Bob Ward, who is also a long time SQL Server expert, teaching you how to leverage the cloud for SQL Server. On day three, Anna does a couple of demos on the subject of HA that demonstrate the capabilities of the Azure platform.  Other days have demos of other concepts as well.

Not only will you learn specifically about SQL Server in Azure, but this series is going to teach you other foundational concepts about Azure that are applicable no matter what you’re trying to do in Microsoft’s cloud. Things like cloud networking and the security aspect that needs to go into that networking, as well as how to give people access to resources in a secure way, are relevant for every part of Azure. With that, let’s dig into what day 3 had to offer.

Day 3 of the Azure SQL Boot Camp was all about availability.  When done right, availability solutions keep your environment up and running when things don’t go right. Here are the key takeaways for Day 3 regarding high availability:

  • Azure has you covered in terms of backup/restore.
  1. A full backup is taken weekly.
  2. A differential backup is taken ever 12 hours.
  3. Log Backups are taken every 5 to 10 minutes.
  4. Backups are stored on Read Access Geo-Redundant Storage, which means there are multiple copies of backups in differing locations.  This protects you from losing the backups.
  5. Point in time restore is available.
  6. Backup integrity checks are peformed and there is even automated, random testing of those backups via automatically restoring some of them from time to time.
  7. Backup retention is by default at 7 days.  You can go up to 35 days or even use the Long Term Retention service and keep backups for as long as 10 years.
  8. With Managed Instance a copy only backup option is available.
  9. On premises backups can be used to restore to a Managed Instance as part of a migration strategy.

Let’s be honest, how many of you have a backup strategy that is this solid? Some of you have databases that are in full recovery model with no log backups happening. Yikes! Don’t believe me? Check into your backup history information in msdb with a little T-SQL and you can verify for yourself whether your environment is up to snuff in this regard.

How many of you are writing backups to the same location as the data? And, the big question is this: How many of you are testing whether you can use your backups to perform a restore? Remember, just because you have backups doesn’t mean you can restore.

If you don’t have these sorts of things in place, you might be doing a lot of mopping up after a disastrous situation. “Clean up on aisle 4!”

  • Azure has you covered when it comes to High Availability
  1. In the general purpose tier, Azure customers get a service that behaves like a SQL Server Failover Cluster Instance.
  2. Auto failover is possible and is handled by SQL Server and the Azure Service fabric.
  3. There is local SSD and local Tempdb with data files in premium storage.
  4. Backup files are geo-redundant.
  5. In the Business critical tier, Azure customers get Availability Groups behind the scenes – set up and configured for you automatically. With this service, you get 3 secondaries, only one of which has to harden transactions in order for the primary to commit transactions, and one replica for read only routing.
  6. In the hyperscale tier, you get snapshot backups taken by Azure, up to 4 secondaries and they can all be readable, as well as redundant data and log files through the log service and long term storage on Azure Standard Storage.
  7. Services like Zone Redundant Configuration, or Availability Zones, provide additional HA services for Azure SQL DB, but not in all regions.  This method of HA is currently not available for Azure Managed Instance.
  8. Geo-replication and auto-failover groups are a central piece to HA in Azure. Geo-replication has multiple replicas and supports read scale out. With this service offering, you do have to update connection strings after a manual failover is initiated.  It is asynchronous but you can use a system stored procedure to force synchronous commit prior to a failover. Geo-replication does not allow for failing over multiple databases together, and is available only for Azure SQL DB. Auto-failover groups can failover multiple databases at once with no changes to the application connection string.  It supports read scale out and is available only in Managed Instance.

Again, let’s do a gut check. How many of you are running important or business critical SQL Servers on stand alone servers and your only real option for recovery if the machine has an issue is to create a new SQL Server and restore all your backups? In this scenario, that important or business critical SQL Server doesn’t have a second copy via log shipping or some sort of replication, mirroring or an Availability Group.

Go on…. think about it.  List your important and critical business servers in a Word doc. Now examine the list and ask yourself what would happen if any of those servers died and couldn’t be recovered.  Would some part of your business stop while you tried to build a new SQL Server and restore? If so, I think you just found an urgent action item to put at the top of your to do list.  As you determine your solution, think about whether some form of SQL Server in Azure can give you what you need.

This is an important feature that is on by default in Azure.  It is available in SQL Server 2019 on-premises, but it is not on by default. It uses a persisted version store to house old data rows.  Those old data rows, and the most recent checkpoint, are used for a faster rollback.  This addresses the problem of a long rollback from a long running transaction that was killed.

Well, that is the summary for Day 3 of the Azure SQL Boot Camp. There is more that was discussed, but remember, I am only trying to give you a summary of key takeaways. Enjoy as you dig into SQL Server in the Azure cloud!