MSP360 For IT PROs
Articles about cloud backup, cloud storage and more
BLOG MENU
Don’t Miss New Articles!
Thank you for subscribing!
Every month we deliver quality content about cloud storage, backups and security. Leave your email to get weekly and monthly digests

Microsoft SQL Server Backup and Restore Guide

Microsoft SQL Server Backup and Restore Guide

In this guide, we will discuss SQL Server backup types, recovery models, as well as best practices that you should take into account when putting together your backup strategy.

The goal of this article is to provide a high-level overview of SQL Server backup and recovery. For details on the topics discussed below, refer to the articles that each section links to.

Table of Contents

    SQL Server Backup Types

    Microsoft SQL Server supports five types of backup: full, differential, transaction log, tail log, and copy-only backup. We will focus on the first three types in this article, as they are the most common.

    Full Backup

    A full backup is a complete backup of your SQL Server database. It backs up all of the objects of the database: tables, procedures, functions, views, indexes, etc.

    You can create a full SQL Server database backup using SQL Server Management Studio, Transact-SQL, or PowerShell (Microsoft offers a detailed guide here). However, if you would like to simplify your backup management, as well as use cloud or hybrid backup storage, see how CloudBerry Backup can help you in this article:

    Further reading SQL Server Database Backup with CloudBerry

    With a full backup, you will be able to restore a database in exactly the same form as it existed at the time of the backup.

    A full backup is the foundation for every other backup type; it must be performed at least once before you can run any of the other types of backups.

    Differential Backup

    A differential backup contains only the data that has been changed since the last full database backup was created. Creating differential backup usually takes less time than a full backup, because you back up only modified data instead of backing up everything.

    However, when you create multiple differential backups, each subsequent differential backup contains additional modified data compared to the previous ones, and is, therefore, larger in size. It might eventually approach the size of the full backup, which will lead to longer restore times (since the full plus the differential backup must be restored). To prevent longer backup times and keep differential backups from getting too large, you need to run new full backups at regular intervals.

    Learn more about differential backups and see how to perform them using built-in SQL Server tools and CloudBerry Backup in this article:

    Further reading SQL Server Differential Backup

    If a database restore is needed, you’ll need to restore the full backup and the differential backup that is closest to the time at which the issue occurred (all of the other differential backups can be ignored). This will allow you to restore your data to a more current state than if you had only a full backup of the database that was not created as recently.

    FREE WHITEPAPER
    MS SQL Server Backup and Recovery
    Check out the ultimate guide with best practices for MSPs:
    New call-to-action
    Exchange WP icon 

    Transaction Log Backup

    A transaction log (T-log) backup is the most granular backup type in SQL Server because it backs up the transaction log which only contains the modifications made to the SQL Server database since the last transaction log backup. It’s effectively an incremental backup.

    You can perform a transaction log backup as often as every few minutes, which will allow you to perform a point-in-time restore and minimize data loss.

    Further reading SQL Server Transaction Log Backup

    The image below illustrates how your SQL Server backup chain might look if you use all three types of backup described above.

    Point-in-Time Restore

    If you are performing regular transaction log backups, you can recover to a point right before a problematic transaction occurred, such as an incorrect deletion or update of data in a table. To perform a point-in-time restore you’ll have to do the following:

    1. Restore the last full backup.
    2. (Optional) Restore the differential backup that is closest to the moment of failure.
    3. Restore transaction log backups in sequence made from the last full backup (or from the last differential backup if you are using them) and the failure.

    SQL Server Recovery Models

    There are three types of SQL Server database recovery models: Simple, Full and Bulk-Logged. The database recovery model determines the following:

    • How long to keep data in the transaction log
    • Which types of backups you can perform
    • Which types of database restore you can perform

    Simple Recovery

    For databases using the Simple Recovery model, SQL Server automatically truncates the log on checkpoint operations, freeing up used space in the transaction log for additional transactions. When using Simple Recovery, transaction log backups are not supported.

    In terms of transaction log backup management, this model is the simplest, but it eliminates the ability to perform point-in-time restores of databases. If your data changes frequently, and you’re running infrequent full and differential backups, this can result in unacceptable data loss if a database needs to be restored.

    Point-in-time restores are not supported and you can only restore the database up to the time of the latest full or differential database backup. The frequency of these backups determines how much data loss you may experience if a database using the Simple Recovery model needs to be restored.

    As an example, you will be able to restore to a more current point in time if you run differential backups every 4 hours as compared to only running a full backup once a day. Data loss depends entirely on the frequency you execute full and differential backups.

    Full Recovery

    Under a Full Recovery model, all transactions remain in the transaction log file until you run a transaction log backup. The transaction log will never be auto-truncated as would occur under the Simple Recovery model.

    With the Full Recovery model, you can recover your database to any point in time within a transaction log backup. As an example, if you run transaction log backups every 30 minutes, you can recover a database to the 15-minute mark within a transaction log backup; before that delete or update statement incorrectly changed data in the database. Data loss is minimized.

    If you are using the Full Recovery model for your database, you should keep in mind that the transaction log continues to store information as changes are made to the database. In order to prevent your transaction logs from growing to enormous sizes and potentially filling up your disk drive, you need to perform regular transaction log backups. Once the transaction log backup is complete, the information that was backed up from the transaction log is cleared and space can be re-used for new transactions.

    It’s important to note that the size of the transaction log on disk will not change, and you should not expect it to. Transaction logs should be pre-sized based on expected activity and as a preventive measure can be set to auto-grow if the available space in the transaction log is used up. You should avoid shrinking these files using SQL Server T-SQL commands unless absolutely necessary.

    Bulk-Logged Recovery

    The Bulk-Logged Recovery model is similar to Full Recovery except that certain bulk operations are not fully logged in the transaction log (this is called minimal logging). Operations like SELECT INTO, BULK import, and TRUNCATE operations are examples of minimally logged operations. With the Bulk-Logged Recovery model, your transaction logs may not become as large as they would under the Full Recovery model.

    The downside, however, is that bulk-logged operations in this model prevent you from performing a point-in-time restore. So there is a potential for more data loss. If you’re not sure that Bulk-Logged is the right recovery model for your needs, it’s recommended you stick with Full Recovery.

    Best Practices

    Keep in mind the recommendations below when putting together your Microsoft SQL Server backup and restore strategy, which should minimize the potential for data loss.

    Develop a backup schedule based on your business requirements
    You need to back up your data regularly. At a minimum, schedule a weekly full backup and daily differential backups. If you use full or bulk-logged recovery models, schedule your transaction log backup frequency based on your RTO and RPO. These metrics reflect how much data loss your business can deal with. If losing more than 30 minutes of database changes is a problem, then make sure to schedule your transaction log backups at least every 30 minutes.

    See how you can set up a schedule for your backups, as well as configure other useful backup and recovery options with CloudBerry Backup in the article below:

    Further reading SQL Server Database Backup with CloudBerry

    Implement backup automation and verification
    Creating backup routines for all your SQL Server databases may require creating multiple backup jobs and schedules, as well as retention management of backup files. This can get complex and require a lot of administrative work.

    You can use the built-in SQL Server tools, but often, third-party products can help automate these tasks.

    Learn what you can do to simplify your daily routine with built-in SQL Server tools and see how CloudBerry software can help you automate SQL Server backup in this article:

    Further reading SQL Server Backup Automation and Verification

    Do not store all SQL Server backup files on the same site as a production database
    For best disaster recovery protection, it's best to keep local and off-site (cloud) backups of your databases. Check out how CloudBerry Backup can provide both on-site and off-site cloud backups of SQL Server databases in this article:

    Further reading SQL Server Database Backup with CloudBerry

    Featured Product

    CloudBerry offers an easy-to-use and reliable solution for SQL Server backup to the cloud or local storage.

    Compression and encryption

    Compression and Encryption

    Compression allows you to reduce storage space (and thus save money) while improving backup time. With AES-256 encryption, you can be sure that all your backup files are protected.

    Cloud and localCloud and Local

    CloudBerry Backup allows you to store your backups on local storage and any of more than 20 cloud storage providers, including Amazon S3 and Amazon Glacier, BackBlaze B2, Wasabi Hot Storage and Microsoft Azure.

    Flexible retention and recoveryFlexible Retention and Recovery

    Store as many versions as you need for as long as you need with flexible retention settings. Recover the latest version or restore to the point in time of your choosing.

    Bare-metal recovery from USB or ISO fileEmergency Recovery for Windows Server

    Protect and restore entire servers using CloudBerry’s image backup and bare-metal recovery features in case of a system or hardware crash.

     

    MSP360 Backup for MS SQL Server
    • Full, differential and transaction log backup
    • On-site and off-site backup options
    • Image-based and system state backup
    New call-to-action
    MSP360 Backup icon