Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Understanding SQL Server 2016 Backup Options
All companies--even those running highly reliable systems like the HPE Superdome X --need to insure that critical data is protected and can be recovered in the event of an emergency or unforeseen disaster. Understanding and implementing one of SQL Server 2016’s backup technologies is core to putting in this critical backup foundation in place.
Michael Otey
July 21, 2016
3 Min Read
Maximum availability is the goal for almost all enterprise database implementations. To achieve that availability, you need to implement a layered approach to your infrastructure. First, you need to build your infrastructure out on an enterprise-level server platform. The HPE Superdome X, for example, provides five 9s (999.99%) of availability out of the box. Next, you need to leverage availability features in the database itself. For instance, with SQL Server 2016, this means using technologies like AlwaysOn Failover Cluster Instances (FCI) or AlwaysOn Availability Groups (AG) to ensure that your database remains available in the event of a system failure. But, even with this highly available foundation in place, there’s one thing that is critical to availability: database backups.
All companies--even those running highly reliable systems like the HPE Superdome X --need to insure that critical data is protected and can be recovered in the event of an emergency or unforeseen disaster. Understanding and implementing one of SQL Server 2016’s backup technologies is core to putting in this critical backup foundation in place.
SQL Server Recovery Models
SQL Server backup and restore operations occur within the context of the recovery model of the database, which controls whether the transaction log requires backing up and what kinds of restore operations are available. SQL Server 2016 supports three different recovery models:
Simple: Suitable for smaller organizations and simpler departmental implementations, this model doesn’t use log backups. Point-in-time recovery is not supported, and you can restore only up to the last full backup. This model is not compatible with Log Shipping, Always On or Database Mirroring.
Full: Intended for larger organization and enterprises, this model requires transaction log backups and fully supports specific point-in-time recovery.
Bulk Logged: An alternative to the Full recovery model, this model requires log backups and reduces log space usage through minimal logging for most bulk operations. It should be used only intermittently to improve the performance of large-scale bulk operations. Point-in-time recovery is not supported.
SQL Server Backup Types
Backing up your SQL Server databases is essential for protecting your data. This is true even if you’re using a high-availability technology like AlwaysOn AGs. SQL Server 2016 supports three basic types of database backups:
Full: A Full backup copies all of a database’s files and filegroups.
Differential: A Differential backup copies only the data that has changed since the last full or differential backup.
Transaction Log: Intended for point-in-time recovery, a transaction log backup includes all log records that were not backed up in a previous full backup or in the last transaction log backup. Most organizations run many transaction log backups per day.
SQL Server 2016 provides the ability to compress and encrypt the database backups. You can also backup specific database files or file groups.
Cloud Backup Options
SQL Server’s built-in backup options support disk, tape and the cloud as backup devices. Thanks to its inexpensive storage and the innate ability to provide offsite protection, cloud backup has become a popular alternative or addition to on-site backups. SQL Server 2016 provides two cloud backup options.
Backup to Azure: Also called Backup to URL, SQL Server 2016 can use Microsoft Azure as a backup target. Backup to Azure is supported by both T-SQL and SQL Server Management Studio (SSMS). The maximum Azure backup size for SQL Server 2016 is 12.8 TB.
Managed Backup to Microsoft Azure: SQL Server Managed Backup is another option to back up SQL Server databases to Azure. Managed Backups are really designed for SMBs and smaller organizations that don’t have a lot of SQL Server expertise. Managed Backup automates the backup process. Microsoft Azure is the backup target, and the backup frequency is based on the retention period and the transaction workload of the database.
HPE and Microsoft are the underwriters of this article.
About the Author
You May Also Like
