SQL Server Backup and Restore in a Veeam environment

The purpose of this paper is to describe SQL Server backup in general and also the options you have for backing up your Microsoft SQL Server databases. The paper is both targeted to the SQL Server DBA, as well as the backup operator who may have less experience with SQL Server. This is not intended to be a reference paper covering all the options in the graphical user interfaces (GUIs) or the SQL commands.

The recovery model setting

A database option called the recovery model, is all about management of the transaction log. The available modes are full, simple and bulk logged. Most installations and databases are either in simple or full recovery model. The default value — what you get when you create a database — is inherited from the model database, and by default is in full recovery.

Simple SQL Server recovery

This recovery model is designed to be used when you do not perform backup of the transaction log of the database. In simple recovery, it is not your responsibility to “empty the log” (or “truncate the log”), as SQL Server will do that for you. However, you can still end up with large ldf files due to long-running transactions and problems with the log reader when using transactional replication. Since SQL Server will truncate the log for you, you cannot perform backup of the transaction log — the BACKUP LOG command will return an error if you try.

The log is typically truncated when a checkpoint occurs (reference the first paper), which is done automatically now and then. You can even stress this using the CHECKPOINT command.

Full SQL Server recovery

In full recovery, it is your responsibility to truncate the log. This happens when you perform backup of the transaction log, i.e., the BACKUP LOG command will truncate the log after producing the backup. It is worth mentioning that other backup types (full, differential, snapshot, etc.) do not empty the log — only log backup will do this. If you are in full recovery and do not take a log backup, then the log file will continue to grow until it reaches maximum size, or the disk is full.

SQL Server backup types

Full SQL Server backup

A full SQL backup includes everything in the database. SQL Server will copy all of the data in the database’s data files (all extents) to the backup destination, which is typically a file. Changes that are made to the data while the backup is running are reflected in the transaction log, and when all data (all extents) have been copied, SQL Server will then also copy the log records that were produced while the backup was running. When you restore from a full backup, SQL Server will copy all pages from the backup file into the data file(s), and all log records from the backup files into the ldf file(s). And finally perform the same type of recovery as when you start SQL Server (see the first paper in this series). For example, you start a full back up at 02:00, the backup finishes at 02:45. So, when you restore from that backup, the database will look like it did at 02:45 — not 02:00. A full backup is performed using the BACKUP DATABASE command.

Differential SQL Server backup

A Differential SQL backup is very much like a full backup, except that SQL Server will only backup the extents that have been modified since the last full backup. It also uses the log records produced while copying the extents, the exact same way as for a full SQL Server backup. For example, let’s say you have a full backup F1 and then differential backups D1, D2 and D3.

SQL Server Transaction log backup

Transaction log backup is defined as backing up the changes made since the last transaction log backup. This option is similar to incremental backup. Technically, SQL Server reads the log records in the ldf file and copies them to the backup file. Log backups have several advantages. First, you can produce a log backup even if the database files are damaged or even lost (using the NO_TRUNCATE option for the BACKUP LOG command). In many cases this means you can achieve zero data loss in the event of an accident. Another advantage is the possibility to perform log backups very frequently, perhaps every hour, every 10 minutes, or five minutes.

The COPY_ONLY option

Sometimes you produce a backup to simply get a copy of a database to restore on a test-server, for instance, especially if you want to avoid impacting the chain of your scheduled backups. For these purposes, we have an option for a backup command named COPY_ONLY. This is relevant for two backup types:

• COPY_ONLY used with full SQL Server backups.
• COPY_ONLY used with transaction log backups.

More advanced SQL Server backup options

There are other backup options which we will not explain in this document — being a document about Veeam and SQL Server backups. These other options are well described in the SQL Server documentation. They include backup at the file or filegroup level.

Scheduling SQL Server to perform its own backups

Scheduling SQL Server to perform its own backups is probably what most experienced SQL Server DBAs will initially be most comfortable with. Let me first say that there are several advantages to backing up your SQL Server, so I suggest you also read the following section before deciding what strategy to choose. Having said that, if you want to produce your own backups to files, then there are some things you must consider.

Hybrid strategies

You can combine the two strategies outlined above, the “Let SQL Server produce backup files” strategy and the “Use Veeam to back up your SQL Servers” strategy.

The DBA centric hybrid strategy

This strategy has already been described. As long as you make sure the machine snapshot is produced using the SQL Server VSS Writer Service (enable “Enable application-aware processing”) then the machine snapshot is a valid restore option for your SQL Server data.

The backup operator centric hybrid strategy

This strategy means you will let Veeam produce its snapshot (restore point) and also transaction log backups. The question now is whether we can perform this while also performing SQL Server backups to file — and not have SQL Server somehow interfere with the Veeam snapshot and log backups? The answer is simple… Yes, we can!

Summary

You can use Veeam for all of your SQL Server backup operations, or only to produce snapshots of your virtual machines. We suggest the person responsible for the overall backup strategy discuss with the DBA whether or not to use Veeam for your SQL Server data. There are several advantages to allowing Veeam to handle your SQL Server backups, including storage space savings on both the backup server and your SQL Server virtual machines, and a single interface for all of your backup and restore operations, etc.

Download the full version to learn more about SQL Server backup!