Backup and DR
SQL Server Backup and Restore: Overview and Best Practices
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 database backup and restore, and outline backup best practices. For details on the topics discussed below, refer to the articles that each section links to.
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.
Further reading Backup Types Comparison
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 MSP360 Backup can help you in this article:
Further reading SQL Server Database Backup with MSP360
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 MSP360 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.
Read some of our most popular articles, like cost-effective backup solutions, what is RMM, why Amazon's EC2 and S3 are fundamentally different types of service, how to resize partitions in Linux, how to retrieve Amazon Access Key ID and AWS Secret Access Key, Exchange online vs. on-premises, immutable backups and what is backup software, before this conclusion!
Learn in our whitepaper:
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 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:
- Restore the last full backup.
- (Optional) Restore the differential backup that is closest to the moment of failure.
- 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.
SQL Server Backup Best Practices
Keep in mind the recommendations below when putting together your backup and restore strategy, which should minimize the potential for data loss.
Further reading Backup Best Practices for MSPs
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 MSP360 Backup in the article below:
Further reading SQL Server Database Backup with MSP360
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 MSP360 software can help with automation in this article:
Further reading SQL Server Backup Automation and Verification
Do not store all backup files on the same site as a production database
Keep local and off-site (cloud) backups of your databases for best disaster recovery protection. Check out how MSP360 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 MSP360
SQL Server Backup and Restore with MSP360
MSP360 offers an easy-to-use and reliable solution for SQL Server database backup and restore.
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 Local
MSP360 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 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.
Emergency Recovery for Windows Server
Protect and restore entire servers using MSP360’s image backup and bare-metal recovery features in case of a system or hardware crash.
Simple. Reliable.
