SQL Server 2016 Backup and Restore Enhancements

Author by Jes Borland

Backups and restores are the bread and water of a DBA’s job. With each new version of SQL Server, Microsoft continues to add enhancements to backups and restores. With SQL Server 2016, here is what you have to look forward to!

Backup to Microsoft Azure

Since SQL Server 2012 SP1 CU2, we’ve had the ability to back up to Azure using the TO URL command. This enabled us to back up to Azure blob storage, which is nearly limitless, and very low cost. In 2016, we will be able to use block blobs instead of page blobs. What does this mean for you?

  • Block blobs can be up to 200 GB and backups can be striped across multiple blobs. This increases the size limit from 1 TB to 12.8 TB!
  • Block blobs are less expensive per month than page blobs.

Learn more about the types of blobs here, and learn how to use Backup to URL with these resources:

This feature is the preferred method for backing up SQL Server VMs in Azure, and can be a low-cost backup strategy for on-premises SQL Servers if the latency isn’t an use.

Snapshot Backups for Database Files in Azure

With traditional full, differential, and log backups, the restore sequence is:

  • Restore full backup
  • Restore most recent differential backup, if applicable
  • Restore all log backups since most recent differential or most recent full, in order

This is cumbersome, but ensures transactional consistency. It can be scripted, but requires thorough testing.

SQL Server 2016 running on Azure VMs will change this by allowing you to take snapshot backups. To do so, use the new BACKUP DATABASE…WITH FILE_SNAPSHOT and BACKUP LOG…WITH FILE_SNAPSHOT commands. When each log backup is taken, a new snapshot is created that encompasses all of the database files.

Where this becomes attractive is during a restore operation. Rather than needing to restore each log backup in turn, you can restore from one file snapshot set. This can reduce the restore sequence from dozens of commands to one.

Can you still restore to a point in time? Yes! By restoring the last log backup snapshot before the point in time with NORECOVERY, then restoring the log backup snapshot that contains the time you want using STOPAT, you can restore to a specific point in time.

The most notable restriction is that this only applies to Azure VMs for now. Snapshots will also incur charges, as most storage in Azure does. However, this is still a feature worth looking into!

Managed Backup

Managed Backup to Azure was introduced in SQL Server 2014. The goal of this feature is to take away the work involved in planning a backup strategy and designing scripts to manage it. You choose only how long you want the data retained and where to store it, and SQL Server schedules, performs, and maintains the backups.

There were limitations to the feature in SQL Server 2014, but many have been removed in SQL Server 2016. Among the improvements:

  • Databases in Full, Bulk logged, and Simple recovery model are supported.
  • System databases can be backed up with managed backups.
  • Block blobs are now used, with the same benefits as listed for Backup to Microsoft Azure.

The biggest change may be scheduling. Prior to 2016, backups were always based on the transaction activity in the database. Now, using the advanced configuration stored procedure managed_backup.sp_backup_config_schedule, you can choose days of the week to run the backups, and frequency of log backups.

No Excuse Not to Have Backups

With the options available to you in SQL Server 2016, there’s no excuse for not having backups of all of your databases, and knowing how to restore them.

Need help getting started with Azure? Contact us today!

Author

Jes Borland

Senior SQL Server Engineer