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.

How Do You Find Good SQL Server Training Resources?

What process can you use to find SQL Server training that will be valuable? After all, not everything on the internet can or should be trusted.  If you want training on something in this constantly changing world of technology, how do you locate that training?  How do you find information that is worth spending your time on?  How much of it costs something?  How much of it is free and is the free stuff any good?

I live in the Microsoft world in general and specifically in the SQL Server universe so that is where I am going to focus.  But, to be clear, this process, and the methods I am going to talk about, will be relevant even if you are a C# Developer or some other kind of IT knowledge worker that has little or nothing to do with databases. I would be surprised if Linux and other open source software didn’t have some equivalencies to what I am going to discuss later on.

Let’s suppose you’ve been sitting next to the SQL Server for awhile and you’ve fixed a few things from time to time.  Congratulations!  You are now what is called an “Accidental DBA.”  You’ve fallen, quite by accident, into managing SQL Server in some capacity.  You’re sensing, though, that there’s a lot more that needs to happen and you have no idea where to start to find information.  After all, when you Google “SQL Server” you get back about a million hits and you don’t know who is a reliable source of information or what you should focus on first.

  • Microsoft MVPs

Have you heard of Microsoft’s Most Valuable Professional program?  If not, this is a good entry point for finding quality content. A Microsoft MVP is a person who has invested significant time into making the Microsoft community a better place.  They do this through three main avenues.  First MVPs may make significant contriubtions to coding projects.  Second, they certainly spend time helping others.  This usually comes from answering questions at places like MSDN, StackExchange.com or SQLServerCentral.com. Third, they create content.  This comes from doing things like blogging, writing articles, and publishing written materials.

So, what does this have to do with training? I’m glad you asked. Remember how we want to know how to sift through all the hits on the internet when you type in something like “SQL Server training”? You want to find the good stuff, right? Well, since you want to learn about something in the Microsoft world, why not find out who the MVPs are and learn from them?  Many of them have their own websites where they create content. Some of them will have special training that they have created on a variety of subjects.  The blog content is going to be free.  Usually you’ll need to fork over some money for the special training they have made.

Here is how to find Microsoft MVPs.  Go here and click the link at the top of the page for “Find an MVP.” For the SQL Server category, select Data Platform from the “Award Category” drop down. If you want to see MVPs in your area, hit the “Region” drop down and select your region to filter the results. From there, click on the name of an MVP and you will be directed to a profile page that will provide a summary of that person.

The summary page for an MVP will show where they have presented and what the presentation title was.  The profile summary will often have links to the person’s blogging locations as well, their Twitter account, and even books they have written. Want to see if someone you follow is an MVP?  Type the person’s name or part of their name into the Keyword box and click “Search”. Beyond the information on the MVP site for a particular person, sharpen your internet search skills and type in the person’s name.  See what you can find!

  • Paid Video Training

  • Pluralsight – In the interest of fairness in terms of the comparison to CBTNuggets, you should know that I’ve had a Pluralsight subscription of my own for years.  I have only in the last year or so learned about CBTNuggets.

On Pluralsight you will see courses from SQL Server people like Paul Randal, Kimberly Tripp, Erin Stellato and others from SQLSKills.com. People like Gail Shaw, Pinal Dave, and Mike MCKeown also have SQL Server courses on Pluralsight.  The platform has reasonably priced monthly and yearly plans. Personal plans on Pluralsight are $29 a month.  You can download exercise files that allow you to work through the content as you watch the presenter or later on your own. The platform also has skills test for a variety of subjects.

A couple of my favorite features  are called Paths and Channels.  Paths are pre-built collections of content that Pluralsight provides to you for learning a topic.  You can subscribe to Paths and you can combine a pre-built path with other content you’re interested in via the creation of a Channel.  Let’s say you’re interested in a group of courses that aren’t part of a path.  What do you do?  Well, don’t fret. That’s what Channels are for.  You can build you own custom path by adding courses to a Channel that you name.  You can also add a pre-built Path from Pluralsight to your custom channel.

Pluralsight also tracks weekly minutes spent watching videos so you can see how much time you’re spending on training and there is a history page so you can see what courses you’ve watched and how much of that course has been viewed.

  • CBTNuggets – At $59 per month, this platform is ore expensive than Plauralsight.  However, CBTNuggets has a few features that Pluralsight doesn’t.

The CBTNuggets platform offers virtual labs that can be spun up from directly inside a course. Additionally, there are IT Certification practice exams from Kaplan as part of the monthly subscription fee.

They also have accountability coaches.  These coaches help subscribers stay on track with their learning goals and they provide additional resources for learning the material as well as learning strategies.

The SQL Server  training on this platform is geared toward the Microsoft Certifications and are taught by people I am less familiar with.  Most of these certifications retire on Jan 31, 2021.  It doesn’t mean there is no value in taking these courses after that date, as I’m sure you would learn a lot from the courses, assuming CBTNuggets keeps them up after the certification expiration.  I certainly think these course could be helpful for someone trying to earn a Microsoft certification before Jan 31, 2021.

 

  • Free Video Learning

There is a growing body of great, free training on the internet.  When looking for this type of learning, search for people who are Microsoft MVP’s in the area you want to train for.

  • Search YouTube for MVPs or well known people who have advanced knowledge of a topic you’re interested in and subscribe to their channels.  YouTube also has playlists of pre-aggregated content on a particular topic.  You can search for playlists by topic like “SQL Server DBA Playlist” and find many playlists to save and watch later. Below shows a playlist for Wise Owl Training on SQL Server Reporting Services. Notice the icon in the lower left corner?  The three dashes and the + sign allow you to save this playlist to your YouTube page.  Then the playlist will appear on the main YouTube home screen on the left.

Here is a sample from my own YouTube playlists. I am not currently watching all of these, but they are playlists I definitely intend to watch.  When I need training on a topic, these playlists provide a handy way for me to find something quickly.

  • Find companies that offer free video training on topics of interest. For example, PragmaticWorks has free videos on a wide range of SQL Server topics. RedGate has a lot of free stuff on SQL Server and Dev-Ops for the database.
  • Find websites of individuals who blog about topics of interest. Besides the blog, some of those people are likely to make videos that are free to watch. This is true of Brent Ozar and Kendra Little, for example.

 

  • Free Online Conferences

Free online conferences are popping up like crazy now that in-person events are being shelved because of Covid.  More and more vendors are assembling virtual conferences, many of them free.

  • EightKB – This is a brand new SQL Server internals conference.  So far they have hosted one virtual event, and I attended.  The content was amazing.
  • Groupby.org – This was initially started by Brent Ozar but is now being headed up by Blythe Morrow.  This is event is held quarterly and has excellent speakers from around the world.
  • “New Stars of Data” – This is a brand new platform launched in 2020 that provides a place for up and coming data experts to speak.

 

  • Paid Conferences

  • DevIntersection – This is a yearly conference hosted in Las Vegas.  It is a broad conference, covering a wide variety of technology.  There is a SQL Server specific track called SQLIntersection. Early bird registration for this event runs anywhere from $1,700 to just over $3,000 depending on how many workshops you attend.
  • PASS Summit – The virtual event costs around $1,000 this year.  They are hoping to be back to in-person events on 2021.
  • SQLBits – This is a data platform conference focused on the SQL Server platform that is hosted in the UK.  Current prices and currency exchanges place the cost of a 2 day conference and 3 workshops at just over $,1000 US dollars.  It is a virtual conference this year.
  • DataGrillen – This in-person conference was cancelled for 2020.  It is hosted in Germany.

 

  • Blogs/Websites/People

  • Brent Ozar: Microsoft Certified Master for SQL Server who focuses on making SQL Server faster and more reliable (i.e. he does performance tuning)
  • Kendra Little – Microsoft Certified Master for SQL Server and now also blogs about DevOps for the Database for RedGate Software.
  • Paul Randal and friends – Paul Randal and Kimberly Tripp worked at Microsoft and are now married.  The site SQLSkills.com was founded by Kimberly and she and Paul run that site and company.
  • Itzik Ben-Gan: Itzik is a co-founder of SolidQ, a company that provides training on leveraging the T-SQL language to solve query problems.  If you want to learn T-SQL inside and out, look up this company and find resources from Itzik.
  • DBATools – Chrissy LeMaire and others have built numerous PowerShell commands into a powerful module that can be used to manage most aspects of SQL Server.
  • SQLServerCentral – I was first introduced to this site because of the forums where a lot of well-known SQL Server experts hang out and answer questions.  But, they also have a lot of training series articles on a wide variety of topics that they call their “Stairway Series”.
  • Microsoft Learn – This is Microsoft’s newest iteration of it’s free training platform. It provides a lot of information on the newest Microsoft certifications as well as free training paths to help users earn a certification. There are a number of data related paths, primarily connected to learning Azure.

Reader, we haven’t even talked about books, classes that might be held at a training center in your local area, or SQLSaturdays.  There is a wide range of means for you to obtain training and help on the SQL Server platform.

  • What to do next

  1. Find at least one sub-point under each of the above main bullet points and investigate that item thoroughly.  Look up MVPs and start following one of their blogs. Investigate YouTube and save some playlists. Check out Microsoft Learn. Pick one thing from each main category above.
  2. Use those selected items to build a training plan for yourself.
  3. Put that training plan somewhere so that you will see it, everyday. Mine is on the white board behind me in my home office.

 

 

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!