[From Technet] SQL Server 2016 cloud backup and restore enhancements

This post was authored by Eli Fisher, Program Manager, SQL Server.

By backing up to the cloud, you can easily store a copy of your data offsite, allowing you to take advantage of cost-effective storage options. In SQL Server 2016, we have added significant enhancements to our backup technologies, making it easy to leverage the cloud for backups.

Backup to URL

With traditional on-premises backup strategies, storing backups can be difficult for reasons such as storage management, storage device failure and making backups geographically redundant. SQL Server Backup to URL allows you to easily backup directly to Microsoft Azure Blob Storage, removing the need to manage hardware for backups while also giving you the benefit of storing your backups in flexible, reliable and virtually bottomless storage.

In SQL Server 2016, we now support the use of block blobs to make this tool even more helpful.

• Backup striping can be used to support backup sizes of up to 12 TB.
• Faster backup and restore when using backup striping.
• You can use shared access signatures to create SQL backup credentials that limit SQL Server’s access to a specific container, rather than the whole storage account.

Managed Backup

Currently, automating backups for multiple databases requires developing a backup strategy, writing custom code and scheduling backups. Using SQL Server Managed Backup to Azure, you can easily create a backup plan by specifying the retention period and the Microsoft Azure storage location. SQL Server Managed Backup to Windows Azure does the rest – scheduling, performing and maintaining the backups.

In SQL Server 2016 we have enhanced this feature to make it even easier to back-up your databases.

• Full, bulk-logged and simple recovery models are all supported.
• System databases can be configured for backups.
• Backup striping can be used to support backup sizes of up to 12 TB.
• Customer backup schedules can be specified to ensure your backups are created when it is best for your workload.
  Getting started with SQL Server 2016 Managed Backup is simple with our configuration guide.

You can also configure Managed Backup on a SQL Server Azure Virtual Machine with our Auto Backup feature. Simply follow the screenshot below when creating your virtual machine.

File-Snapshot Backup

With traditional SQL Server backups, you need to stream entire copies of your databases and their logs into backup files. This can be both a time consuming and resource intensive process. When storing SQL Server Data Files directly in Azure Blob Storage, File-Snapshot Backup allows you to backup and restore data quickly, and with minimal impact on performance. As data in the blob changes over time, the snapshots continue to point to the data at the time when the snapshots were created.

Not only does this feature fully support point-in-time restore, but, unlike traditional point-in-time restore (where you need all the transaction log backups from the most recent full or differential backup before the point-in-time), File-Snapshot Backup only requires the backup before the point-in-time and the backup after the point-in-time. The image below compares using File-Snapshot Backup with what is required to restore to a place in the last log backup of a backup chain using traditional streaming backup. Note the difference in the number of backups required for File-Snapshot Backup.

To get started with File-Snapshot Backup, please follow our tutorial on Using Azure Blob Storage service with SQL Server 2016 databases and check out the File-Snapshot Backup Channel 9 video below.

Summary

With SQL Server 2016, we improved our hybrid backup feature set to help you be more productive and secure. With SQL Server Backup to URL we support much larger databases and provide you with more secure storage account backup configurations. With SQL Server Managed Backup we have made it easier to customize your backup configurations and backup your system databases. File-Snapshot Backup allows you to take nearly instantaneous backups and very fast restores of SQL Server Data Files directly in Azure Blob Storage.

Next steps

• Tutorial: Using Azure Blob Storage service with SQL Server 2016 databases
• Enable SQL Server Managed Backup
• File-Snapshot Backup Channel 9 video

See the other posts in the SQL Server 2016 blogging series.

from SQL Server Blog http://ift.tt/1SqP8pV