SQL Backup Mastery: Tips, Tricks, Recommendations

by

To perform a SQL Server backup is very simple — it can be done in 5 clicks via SSMS. But the topic of backups in SQL Server is deep.

Take a seat, young Padawan, for this article will be your first step toward becoming a Jedi Backup Master.

Choose Recovery Model

First of all, you need to understand the cornerstone of SQL Server backups.

SQL Server recovery model is a setting that all databases have. It determines how the database stores the transaction log and, as a result, what backups can be performed.

The transaction log is a file that as a rule is located next to the database data file and usually has .ldf extension. Changes in the database are first written to the transaction file and then transferred to the main .mdf data file.

There are three types of recovery models:

  • Simple Recovery Model — after applying transactions to the main file, the transaction log is immediately cleared.
    • Pros:
      • Does not require additional disk space — everything is cleaned immediately.
      • It’s easy to manage backups.
    • Cons:
      • Only bulky Full backups and Differential backups are available.
      • Point-in-time recovery is not available.
    • When suitable:
      • When data loss during the time period between backups is acceptable. For example blogs or forums.
  • Full Recovery Model — transactions in the file are “cleared” only when the transaction log is backed up. Transaction log backup allows you to perform log shipping, Always On or Database Mirroring, or point-in-time recovery.
    • Pros:
      • Allows you to perform quick and compact backups of the transaction log as often as you need it.
      • Point-in-time recovery is available.
    • Cons:
      • The transaction log file takes up a lot of space, sometimes even more than the main database file.
      • Managing such backups without specialized software is not trivial.
    • When suitable:
      • When data loss is unacceptable, and you need to restore the database state as close as possible to the moment of failure. For example, any database that stores financial transactions.
  • Bulk Logged Recovery Model — similar to the Full Recovery Model, but optimized for bulk database modification operations.
    • Pros:
      • Transaction log backups are available.
      • The transaction log does not grow much during bulk update operations.
    • Cons:
      • The transaction log file takes up a lot of space, especially for non-bulk operations.
      • They are also difficult to manage.
    • When suitable:
      • At the same time as the Full Recovery Model, but if mass updates or deletions often occur in the database.

Full Recovery Model and Bulk Logged Recovery Model allow you to make really cool backups. You can read more about them here.

Learn SQL Backup Statement

The SQL Server backup/restoration command is very simple, but it has many useful options and features that can significantly improve your backups.

Backup SQL Server Database to Local Machine
BACKUP DATABASE [db_name] TO DISK = 'PathToBackup.bak'
Restore SQL Server Database to Local Machine
RESTORE DATABASE [db_name] FROM DISK='PathToBackup.bak'

SQL Server Incremental Backups

An incremental backup is a backup that does not contain the entire database, but only part of the data that has changed since the last backup.

Differential Backup

This is a backup that contains all the changes since the last full backup. These backups can be very compact for a long time — if only a small part of the data in the database is constantly changing. However, if all the data in the database is subject to change, then the DIFF backup will very quickly approach the size of a FULL backup.

Differential backup can be performed in the following way:

BACKUP DATABASE [db_name]
TO DISK = 'PathToDiffBackup.bak'
WITH DIFFERENTIAL;

To restore, you will first need to restore the last full backup, and then restore the differential backup:

RESTORE DATABASE [db_name]
FROM DISK = 'PathToFullBackup.bak'
WITH NORECOVERY;
RESTORE DATABASE [db_name]
FROM DISK = 'PathToDiffBackup.bak'
WITH RECOVERY;

Transaction Log Backup

These backups are available only for Full Recovery Model and Bulk Logged Recovery Model.

The size of transaction log backups is directly proportional to the data that has been changed since the previous full, differential or other transaction log backup.

BACKUP LOG [db_name] TO DISK = 'C:\PathToLogBackup1.trn'

But to restore, you will need to restore the last full backup, and all the transaction log backups that have been made after the full backup.

RESTORE DATABASE [db_name]
FROM DISK = 'PathToFullBackup.bak'
WITH NORECOVERY;
RESTORE LOG [db_name] FROM DISK = 'PathToLogBackup1.trn' WITH NORECOVERY;
RESTORE LOG [db_name] FROM DISK = 'PathToLogBackup2.trn' 9WITH RECOVERY;

Useful SQL Server Options

Backup Compression in SQL Server

As a rule, backups are compressed very well; they can be compressed with a separate tool. But you can do this on the fly:

BACKUP DATABASE [db_name] TO DISK = 'PathToBackup.bak' WITH COMPRESSION;

When restoring, such a backup will uncompress itself on the fly.

Split Backup in Multiple Files

You can split the backup into several files, this can be achieved in the following way:

BACKUP DATABASE [db_name] 
TO DISK = 'BackupPath_Part1.bak',
   DISK = 'BackupPath_Part2.bak',
   DISK = 'BackupPath_Part3.bak';

To restore you will also need to specify all files:

RESTORE DATABASE [db_name] 
FROM DISK = 'BackupPath_Part1.bak',
     DISK = 'BackupPath_Part2.bak',
     DISK = 'BackupPath_Part3.bak';
Why backup into multiple files at all?

This way, you can significantly speed up the speed of creating backups if your hard drive supports parallel access or you make backups to different drives. The time difference can be very significant, because the bottleneck of creating a backup is just writing to disk.

Copy Only

This option makes sense if you need to perform separate backups that will not affect regular differential and transaction log backups.

BACKUP DATABASE [db_name] TO DISK = 'BackupPath.bak' WITH COPY_ONLY;

VERIFYONLY & Checksum

A backup created by SQL Server, of course, can be restored to the same server. However, there is a small chance that during a backup creation, corrupted data may appear (for example, in case of disk writing errors or hard disk wear). It is very useful to identify such errors IMMEDIATELY and not when a backup copy is urgently needed.

The Checksum option adds checksums inside the backup that will be verified during restoring.

BACKUP DATABASE [db_name] TO DISK = 'BackupPath.bak' WITH CHECKSUM;

A backup created with this option does not need to be truly restored. You can run a “test” restore, which will do everything that is needed for the restore, except for writing the database to be restored to disk.

RESTORE VERIFYONLY FROM DISK = 'BackupPath.bak';

The combination of CHECKSUM and RESTORE VERIFYONLY almost completely guarantees that the backup was created without errors, the likelihood of which is always present because such is the nature of server hardware, the stability of which is measured by probabilities.

The downside is that CHECKSUM slightly increases the load on the database during backup. RESTORE VERIFYONLY doesn’t impose as much load on your database as a full restore does since it doesn’t involve actual disk writing. However, in heavily loaded environments, this may still be noticeable. Therefore, it’s recommended to conduct restores in a test environment rather than on a production server.

SQL Shrink Log File

If you use the full or bulk recovery model, then the transaction log into which the database writes transactions can only grow. This file is never reduced in size by default.

This does not mean that it will grow indefinitely. When performing a transaction log backup, the backed up blocks of this file will be marked as available for rewriting.

However, performing a large number of transactions or without a log backup for a long time will bloat the transaction log. This can lead, for example, to low disk space. In this case, it makes sense to remove empty spaces from the transaction file using the following commands:

DBCC SHRINKFILE (log_backup_name);

Please note that it is not recommended to shrink the transaction log “just like that.” This will reduce the performance of the database, since re-expanding the transaction log will not be free. Shrink the file only if it has grown abnormally and is significantly larger than its “normal” size. By the way, SqlBak can track and display on graphs the size of the transaction log and the size of the database file.

3-2-1 Rule

This rule applies to all backups in general and is also suitable for SQL Server backups. This is not just a local rule, but an official recommendation from America’s Cyber Defense Agency, and it goes like this:

  • Keep 3 copies of any important file: 1 primary and 2 backups.
  • Keep the files on 2 different media types to protect against different types of hazards.
  • Store 1 copy offsite (e.g., outside your home or business facility).

Its essence is that you need to have 3 copies of your data — the database itself, as well as two backups. One backup is stored locally for quick recovery, and the second in the cloud.

Retention Period

The issue of storing backups is, on the one hand, trivial — it all depends on your business requirements. However there is a popular and convenient strategy for storing backups, which does not require much storage space and at the same time covers a large time interval. This strategy is called GFS backups.

Here is an example of such a storage policy:

  • Grandfather backups are annual backups, one backup is stored for each year.
  • Father backups — stored one for each month, for the last 12 months.
  • Son backups — daily backups, they are stored one for each month.

Combine Transaction Log backups and DIFF backups

GFS backups work very well with diff and transaction log backups. For example:

  • Grandfather backups are full backups that are made once a month and stored for a year.
  • Father backups are differential backups that are made once a week and stored for 4 weeks.
  • Son backups are transaction log backups that are made once every 6 hours and stored for a week.

Tune SQL Server Auto Backups

The main problem with making backups manually is not that it is labor-intensive, but that it is susceptible to human error.

Be sure to create a backup script that covers your needs; here is a separate article about a backup script for SQL Server.

Or set up automated backups through the SQL Server Agent.

Or use specialized software that automates the process, for example, SqlBak or SQLBackupAndFTP.

Track your Backups

It’s not enough to just set up backups once and forget about them. You should have simple tools for monitoring the backup process, so that if something goes wrong, you can understand it right away, but not when your database needs to be restored.

If you perform a backup script, consider error logging and ways to notify about the successful completion of the backup or errors. Collaboration platforms such as Teams, Slack, Jira – support simple APIs for notifications that can be called from a script.

SQL Server agent supports sending notifications by email and stores a history of running jobs.

Third-party utilities, such as SqlBak or SQLBackupAndFTP, also usually support sending notifications and you can view the backup history in them.

In addition, in SQL Server you can view the backup history with a simple SQL query.

SELECT TOP 100
    s.database_name,
    m.physical_device_name as [Path to backup],
    CAST(s.backup_size / (1024 * 1024) AS INT) AS [Backup Size (MB)],
    s.backup_start_date,
    DATEDIFF(SECOND,s.backup_start_date,s.backup_finish_date) as [Backup Time (sec)], 
    CASE s.[type] 
        WHEN 'D' THEN 'Full' 
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
    END AS [Backup Type]
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
ORDER BY backup_start_date DESC, backup_finish_date

Document Backup and Restore

As a rule, SQL Server is not the only link in the IT infrastructure, and if the server fails, all parts of the system will need to be restored.

Most likely, when you set up an automated backup, you will forget about what you set up for many years. And if you still have to restore the database, then you will need to remember “What did I set there 5 years ago?”

Therefore, make a note how you set up backups and how to restore them. Do this in the form of steps or commands that need to be executed. The main thing is not to store this note on the server that you are backing up. 😊

Consider Using Third-Party Backup Software

People made backups even before the advent of computers, and many good practices were developed on how to approach this issue.

Making a SQL Server backup is a solvable, but not trivial task.

Third-party, paid backup utilities will not completely relieve you of responsibility for the safety of your data, but they will definitely save you time and effort in setting up master backups.

This article was created by the developers of the SqlBak and SQLBackupAndFTP products. We have been doing SQL Server backups for 15 years and have become experts in this matter. Try our products, they are cool. 😊

Leave a Comment