What Every Accidental DBA Needs to Know Now: Backups

I’ll be presenting a session at the 2016 IT/Dev Connections Conference titled (some) of the Top 10 Things Every Accidental DBA Needs to Know. Why “some”? Because the list is actually endless – it’s difficult to say what is considered important for a DBA just starting their professional journey and one who is an established database administrator moving towards Senior DBA status.

In advance of this year’s IT/Dev Connections conference I’ll elaborate on these ten points I intend to cover in my session and will extend this into a regular series of articles aimed at those IT professionals who have found themselves assigned stewardship of their company’s data.

This first two articles cover what are arguably the most important aspects of the Database Administration profession: the ability to recover lost or damaged data through the backup and restore process. I’ve always stated that a successful backup is irrelevant – and I adhere to that steadfastly still. But the reason I say that is that the only thing that matters in backup and recovery is the latter: a successful recovery in the event of loss or corruption of data. That being said though one doesn’t work without the other. The act of backing up data and subsequently restoring your backups in a process that yields continuity of your data is a yin-yang of information technology. Successful restores require solid backup strategies.

Successful backup strategies in Microsoft SQL Server require an understanding of the fundamentals of what types of backups are possible, the understanding of recovery models for databases, a basic knowledge of the use and structure of the transaction log in SQL Server as it pertains to logging and recovering transactions, and syntax. We will cover each of those here in various depths in this first article. The second in this series – published later this week – will cover the recovery / restore side of this symbiotic relationship.

Types of Backups

There are three basic types of backups in Microsoft SQL Server:

Additionally it’s possible to backup files and filegroups in Microsoft SQL Server but those are advanced topics that will not be covered in this series aimed at the new Database Administrator.

Full Backups

A Full backup is essentially a file copy of the .mdf and any .ndf files: the data file(s) for a SQL Server database. The term “Full” can be construed as a complete copy of the database and that is incorrect.  These data files only store the data that has been permanently written to the database. This doesn’t include all transactions necessarily as the transaction log is used for making a record on transactions as their being committed and eventually persisted through a write process to the data file(s). While the Full backup primarily stores copies of all the data files there is a portion of the transaction log (the .ldf file) that is included in a full backup. This portion of the transaction log allows for restoring transactions that are committed to the data files during the backup process and for noting the transactions not yet committed – that are “in-flight” when the backup is being taken. Depending upon the type of restore and the type of recovery model for the database (both topics discussed later in this series and in this article respectively) those “in-flight” transactions will be either rolled back and not included in a restore or they’ll be accounted for in a subsequent recovery step through an additional differential or transaction log restore later during recovery.

It should be noted that just because you take a Full backup of your database you may not be able to recover your database to a specific point in time since a Full backup by itself can only be restored to the time the backup was taken. In order to restore to a specific point in time you’ll need to use a variety of backups: Full, (possibly Differential), and Transaction Log backups against a database in a compatible Recovery Model (covered in the next section of this article).

Differential Backups

Differential backups are similar to Full backups in that they’re representative of the data pages (and with enough transaction log information for the roll-forward/roll-back of committed and pending transactions taking place during the differential backup process). However the scope of the data pages that are backed up in a Differential backup process are only those pages that have changed since the last Full backup. Furthermore, each subsequent Differential backup taken after a Full backup will include all of the pages already included in any Differential backup plus those pages changed since the last Differential backup. This means that with regards to Differential backups:

A differential backup is no good without a previous Full backup and a Full backup must have previously been taken for the database before you’re even allowed to perform a Differential backup. An error will be raised if you attempt to take a Differential backup when no Full backup exists in the backup chain (think “roadmap for restoration” or “metadata history of backup actions” when you hear the phrase Backup Chain) for the database. This is inherent in the definition of a Differential backup as already explained: a copy of all changed data pages since the last Full backup.

Transaction Log Backups

Unlike the other two backup types Transaction Log (or simply “Log” backups) don’t take into consideration the data pages / data files for the database. These backups only cover the transaction log files themselves and specifically the transactions documented since the last Transaction Log backup was taken. Like Differential backups a Transaction Log backup can only be taken if two conditions are met:

When it comes to the ability to restore a database to a specific point in time you can only do so if you’re taking transaction log backups. Even then, as you’ll see, there are some exceptions to that rule.  Depending on the amount of logged transactions, the frequency at which you take Transaction Log backups, and the structure/configuration of your transaction log file the individual transaction log backups can be quite large.

I’ve referred to recovery models and transaction logging a few times now. Let’s spend a bit of time looking at what I mean by recovery model and how the recovery model for a database affects what sort of backup can be taken as well as your options for restoring backups.

SQL Server Database Recovery Models

Recovery model refers to the method in which SQL Server logs transactions to the database and what sort of transactions are logged to the transaction log for the sake of a potential recovery situation. There are three recovery models in Microsoft SQL Server:

Full Recovery Model

A database running under Full recovery is logging all transactions to the database that modify data or change the structure of the database in any way, and persisting those individual transactions to the transaction log before they’re written to the data files. The transaction log can be used – in conjunction with Full and Differential backups – to restore the database to a specific point in time provided you have access to all necessary backup files in the backup chain since the last Full backup prior to that point in time and that those files are healthy and free of corruption. Under Full recovery a transaction log file may become quite large depending upon the frequency of your Transaction Log backups and the type and volume of transactions performed against the database. It’s strongly recommended – a best practice of the highest level actually – that if you’re running under Full recovery you’re taking regularly scheduled Transaction Log backups. Otherwise you run the risk of consuming all free space on the disk volume hosting the transaction log. If this happens you’ll be unable to log any further transactions against the database which is a sure bet for preventing any proper use by your end users and a sure bet for your phone ringing off the hook, your cell phone alerting you in the middle of the night, or a critical visit by your supervisor.

To make this perfectly clear:

If you are running under Full recovery ensure you’re able to take regularly scheduled Transaction Log backups at a cadence that is timed properly for the volume of logged transactions balanced with the amount of space allocated to the transaction log file on the underlying storage volume.

Simple Recovery Model

Under Simple recovery a database is not logging transactions in a fashion like the process under Full recovery. SQL Server is still making use of the transaction log but it’s doing so in a way to persist a transaction that changes data or data structure prior to that transaction’s effect being written to the data file. Essentially, once a transaction is hardened to the data file the transaction log no longer cares about the transaction and “forgets” about it. Under Simple recovery you can not restore to a point in time since SQL Server can’t use the log file as a run book for restoring data to a specific point at which it changed; and the simple fact that you’re not able to take Transaction Log backups while running in Simple recovery.

If a database is not critical enough to require a need to restore to a point in time or is used for some form of non-production use such as Q/A, training, testing, etc. then Simple recovery affords you the ability to be less concerned about transaction log sizing, growth, and storage volumes. You need to size your transaction log to accommodate the largest transactions that may occur but you’ll only see growth in the event transaction size and concurrent occurrence exceeds those estimates under normal circumstances. We will talk more about transaction log structure and growth in the next section of this article.

Bulk-Logged Recovery Model

Under Bulk-Logged recovery you’ll see a blending of Full and Simple recovery models. As a rule most transactions are logged to the transaction log file. However there are a few that can be verbose in their impact to the transaction log that are not. While this keeps the size in check for a transaction log it does mean that you still can encounter all the pitfalls of not backing up a transaction log that you have with Full recovery. It also means that you’ll not be able to perform a restore to a point in time where any of these non-logged transactions are occurring because the transaction log is in effect “incomplete” during that process. The non-logged process will minimally log the fact that one of these non-logged processes are occurring in the log but won’t record all the details. SQL Server will tell you if you’re trying to restore to a point in time that’s not valid if you try to do so.

These minimally logged (aka non-logged) processes include:

Introduction to the Transaction Log in SQL Server

In and of itself transaction logging and the structure, care, and configuration of the SQL Server transaction log is a very complex set of topics that will be covered at an introductory level in the third article of this series. I am wading into the shallow waters of this topic now to provide a level of understanding on how the transaction log plays a role in the backup process and your backup planning.

The transaction log in a SQL Server database allows for recording transactions that either change data or change the structure of your database. These types of transactions are referred to data modification language (DML) queries or data definition language (DDL) queries respectively.  This means that queries that involve reading data – think SELECT Transact-SQL queries – will not be logged to the transaction log since, unlike INSERT, UPDATE, and DELETE queries they don’t change the data values. These are your DML queries – you’re modifying the data values in these types of queries. In DDL queries you’re changing the definition of the data or the data structure in some fashion: index rebuilds for example change the structure of the index for a table and therefore the metadata – the data about the data – is changed. Changing the the data type for a column in the table from an integer to a string would be another example of a DDL query that would be logged.

Effect of Recovery Model and the Transaction Log

I mentioned earlier the concept of minimally logged transactions if you’re running under a Bulk-Logged recovery model. In this case what could be a verbose chain of entries into the transaction log file is “stubbed” with a note that says: “hey, there was an index rebuild happening here so fast forward to when it’s done if you intend to restore to a point in time. The end state of the process will be in effect when you get there.” If this type of activity occurred under Full recovery you could end up with a very large transaction log (and associated transaction log backup file) because the individual metadata changes are logged. Under Simple recovery you’d not see those individual transactions either.

Transaction Log Sizing and Growth Behavior

The transaction log is written to (for processing transaction to the data file(s)) and read from (for restores) in a linear timeline but in a “round-robin” mode that under normal circumstances circles back to the start of the transaction log when the end of the physical file is reached. This means the transaction log file is re-used over and over again so long as the portion of the log used previously has been marked for reuse (aka “truncated”). This truncation occurs implicitly in Simple recovery. Under Full and Bulk-Logged recovery models this truncation can only occur via a Transaction Log backup which will mark for truncation the portion of the transaction log that is persisted for recovery purposes. I’m simplifying this process here because all you really need to know about this from the introductory point-of-view is that truncation allows for transaction log reuse and that the only way to truncate a transaction log is through a Transaction Log backup. This is why you see my stressing the need for Transaction Log backups under Full or Bulk-Logged recovery. Without a truncation through Transaction Log backups your log file never round-robins to the beginning truncated portion and instead just keeps appending to the end of the transaction log file until either a Log backup is taken or you consume all space on the underlying volume. A Transaction Log backup taken after a very long period since the previous Log backup can be very large and can require a significant amount of time to read for recovery sake. It can impact the amount of time it takes to restore the database to a point in time. Additionally when a transaction log needs to be grown through an auto grow event you’re unable to log any new transactions – in effect locking the database from any activity until the file is extended on disk since logged transaction can’t be written to the log file and under most circumstances write actions will block read actions in a database.

Transaction Log Configuration

Without getting too deep in the weeds in this article I wanted to explain the concept of auto growth and file settings for a SQL Server transaction log. While you can have more than a single transaction log there is no good reason for configuring a database in such a way. SQL Server will not stripe against multiple transaction log files and will instead move on to the next transaction log file if one exists and won’t round-robin back to the start of the first transaction log until reaching the end of the last transaction log – taking into consideration all the same caveats about log truncation. As a rule you should place your transaction log on your fastest storage platform formatted for optimal writes with redundancy for mirroring and striping. RAID-5 may be the cheapest option for disk but incurs a parity write overhead that is non-optimal for write speed making it a poor choice for disk formatting. However that tends to be more common than not when storage is provisioned.

Regarding sizing I strongly advocate for provisioning the log file with the full amount of space you’ll expect to need over the lifetime of the database. You should never rely on the ability to set a growth increment value to manage file provisioning – transaction log or data file – in Microsoft SQL Server. Not only does it lead to possible blocking of all activity when a growth event occurs in the log file but it also introduces physical fragmentation into the mix. In the case of spinning disk storage that is an additional performance hit that can be avoided with proper estimates and configuration from the beginning. As insurance against locking all transaction when a log file fills I do still advocate configuring a transaction log for growth with the following values for that growth setting:

It’s quite a balancing act.

I strongly recommend monitoring the available space in all your SQL Server database files – not just your transaction logs. This is baked into most third-party tools for SQL Server but also can be performed through SQL code like the following:

SELECT DB_NAME() AS database_name, [name] AS [logical_file_name],  physical_name,  [file_type] =  CASE type WHEN 0 THEN 'Data'WHEN 1 THEN 'Log'END, [size_mb] = CASE ceiling([size]/128)  WHEN 0 THEN 1 ELSE ceiling([size]/128) END, [available_mb] =  CASE ceiling([size]/128) WHEN 0 THEN (1 - CAST(FILEPROPERTY([name], 'SpaceUsed') as int) /128) ELSE (([size]/128) - CAST(FILEPROPERTY([name], 'SpaceUsed') as int) /128) END, [growth_units]  =  CASE [is_percent_growth]  WHEN 1 THEN CAST([growth] AS varchar(20)) + '%'ELSE CAST([growth]/1024*8 AS varchar(20)) + 'Mb'END, [max_size_mb] =  CASE [max_size] WHEN -1 THEN NULL WHEN 268435456 THEN NULL ELSE [max_size]/1024*8 END FROM sys.database_files ORDER BY [file_type], [file_id];

If you note a NULL value for max_size_mb this is because the value has not been set – it’s essentially “unlimited” but obviously limited by the underlying storage volume.

Basic SQL Server Backup Syntax

With all this being said let’s look at the basic syntax for various backup options before finishing up with backup strategies in this article. I’m going to cover the basic syntax for backups. The options are plentiful for configuring backups so for more detailed syntax and options I’ll refer you to the official Microsoft documentation.

In the syntax below I’m using what is known as Template Parameters. These constructs allow you to re-use code without having to change values that tend to fluctuate each time the query is run. For a complete explanation of Template Parameters and Templates in general please refer to these articles:

The simple explanation is that you can use the keyboard shortcut of Ctrl+Shift+M that will bring up a modal window in SQL Server Management Studio and will swap out all parameters that are bookended by the “<” and “>” characters. I’ve also included the command for backup compression. You may be running under certain versions/editions of SQL Server where compression was not an option. If so, comment out the command when executing.

Basic Full Backup Syntax

BACKUP DATABASE [] TO DISK = N'' WITH  NAME = N'' + getdate(), ,  STATS = 10;

Basic Differential Backup Syntax

BACKUP DATABASE [] TO DISK = N'' WITH  DIFFERENTIAL,NAME = N'', ,  STATS = 10;

Basic Transaction Log Backup Syntax

BACKUP LOG [] TO DISK = N'' WITH NAME = N'',,  STATS = 10;

Backing Up the “Tail of the Log”

There is a special process for Transaction Log backups that you’d employ for what would be a final backup for a database in event of corruption, migration, or some other need to take a final transaction log backup to capture all transactions in-flight while ensuring no other transactions are initiated and possibly missed. This is called the “Tail of the Log backup”. The syntax is almost identical to the base Transaction Log backup syntax with the exception of the NO_TRUNCATE and NORECOVERY commands. These additional commands will avoid truncation of the transaction log and will place the database into a Recovering state which prevents additional backups to occur or sessions to connect. It prevents any access to the database. A tail of the log restore would be the final restore action to capture all activity against a database if you are looking to restore to the most recent point in time with no data loss. It’s also the most frequent error in omission when working on restoring a corrupt or unavailable database to life. Without taking a tail of the log backup you’ll lose any transactions occurring since the last Transaction Log backup.

BACKUP LOG [] TO  DISK = N'' WITH NO_TRUNCATE,NAME = N'', NORECOVERY,  STATS = 10;

Notes on Backup Strategy

When developing a backup strategy it is important to keep in mind the frequency of your transactional load, the type of transactions, and just how much time you’re able to take to restore a database to viability should disaster strike. Additionally, to protect against server failure (and to not keep all those eggs in a single basket) it’s strongly recommended to either backup to a file share on the network, a separate backup device, or copy your backups to a secondary location for safekeeping should you lose your SQL Server instance all together.

In the next article in this series we will be covering how to use these backups to recover a database and build a sample recovery strategy based upon the types of backups discussed here.