Introduction to SQL Server Database Backup and Restore
Backup and Restore enhancements
A robust backup strategy is always required even if an organization employs high-availability, disaster-recovery, and hybrid-cloud strategies to protect its mission-critical data. SQL Server 2014 makes several investments in backup operations, including using Windows Azure for simplified backups and native encryption to protect both on-premises or cloud backups.
Managed backup was introduced in SQL Server 2014, and allowed an automatic database backup to Microsoft Azure, based on changes done in the database. This feature schedules, performs and maintains the backups–all a DBA needs to do is to specify a retention period.
SQL Server backup to a URL
This topic includes best practices and troubleshooting tips for SQL Server backup and restores to the Windows Azure Blob service.
SQL Server 2012 SP1 CU2 enabled SQL Server backup and restore functionality to Windows Azure Blob Storage. Unfortunately, this feature was available only when issuing BACKUP and RESTORE statements with Transact-SQL, PowerShell, or SQL Server Management Objects (SMOs). In SQL Server 2014, the ability to back up to and restore from Windows Azure Blob Storage has been added to SQL Server Management Studio via the Backup And Restore wizard and the Maintenance Plan wizard. Transact-SQL and PowerShell can still be used if you prefer them.
Figure 3.12 illustrates an on-premises SQL Server backup to Windows Azure Blob Storage by using a URL.
FIGURE 3.12 : Leveraging the Windows Azure Blob Storage service to back up an on-premises database to Windows Azure.
Backing up SQL Server databases to the cloud offers a tremendous number of benefits to organizations, including:
- Limitless off-site storage.
- No need for backup media management.
- No hardware management overhead.
- Three copies of the data are stored in Windows Azure for redundancy, with the option for data to be replicated to a secondary location, resulting in Geo-replicated off-site storage.
- Data can be quickly restored to a Windows Azure Virtual Machine in the event of a primary site disaster.
- Encryption keys can be stored on-premises, while the backup files are in the cloud.
- No need to enable transparent data encryption or use third-party solutions to encrypt backups.
To use Windows Azure Blob Storage for backups, a Windows Azure subscription is required. You need to create a storage account and a container in Windows Azure and then create a SQL Server credential that holds critical information, such as the container’s policy information and an access signature that is shared. The next section illustrates how to back up to a URL by using the Backup Task in SQL Server Management Studio. Later, we show how to perform the same operation with Transact-SQL.
Backup to URL with Backup Task in SQL Server Management Studio
SQL Server Managed Backup to Windows Azure and at the same time, automates SQL Server backups to Microsoft Azure Blob storage. You can choose to allow SQL Server to determine the backup schedule based on the transaction workload of your database. Or you can use advanced options to define a schedule. The retention settings determine how long the backups are stored in Azure Blob storage. SQL Server Managed Backup to Windows Azure supports point in time restore for the retention time period specified.
- In SQL Server Management Studio, expand Databases, and then select a user database that you want to back up.
- Right-click the database, point to Tasks, and then click Back Up.
- On the General page, specify the backup options you would usually select, such as backup type and backup component. To use Windows Azure Blob Storage, select URL in the Backup To list in the Destination section.
- For the additional URL options on the page, enter the following information, as illustrated in figure 3.13:
- File Name : Enter the name of the backup file, such as SaleForcast_Backup.bak
- SQL Credential : Enter or select an existing SQL Server credential, or create a new one by clicking Create.
- Azure Storage Container : Specify the name of the Windows Azure storage container where you will store the backup files, such as SalesForcastDB
- URL Prefix : The URL prefix is built automatically using the information specified in the fields described earlier. If you edit this value, be sure it matches the other information you provided. For example, if you modify the storage URL, be sure that SQL Credential is set to authenticate to the same storage account.
FIGURE 3.13 : Using the Back Up Database wizard to backup an on-premises database to Windows Azure using a URL prefix.
- On the Media Options and Backup Options pages, enter additional backup information, such as reliability options, compression, and encryption.
Backing up to a URL by using Transact-SQL
So, if you have chosen to do everything yourself, then below you will find the necessary steps which you have to do in order to backup to Windows Azure storage service.
The following Transact-SQL example illustrates how to back up a SQL Server database to a URL.
BACKUP DATABASE [SalesForcast]
TO URL =
Encryption for backups
This topic provides an overview of the encryption options for SQL Server backups.
Starting in SQL Server 2014, SQL Server has the ability to encrypt the data while creating a backup. By specifying the encryption algorithm and the Encryptor when creating a backup, you can create an encrypted backup file. All storage destinations: on-premises and Window Azure storage are supported. In addition, encryption options can be configured for SQL Server Managed Backup to Windows Azure operations, a new feature introduced in SQL Server 2014.
To encrypt during A backup, you must specify an encryption algorithm, and an Encryptor to secure the encryption key. The following are the supported encryption options:
- Encryption Algorithm: The supported encryption algorithms are: AES 128, AES 192, AES 256, and Triple DES
Encryptor: A certificate or asymmetric Key
For many years, organizations and customers have requested that native encryption for backups must be included along with SQL Server. In the past, encryption for backups could be achieved through third-party solutions or with transparent data encryption (TDE). Third-party solutions added additional costs to the overall solution, and TDE would encrypt not only the backup, but also the whole database, which was not always a requirement of an organization or practical, given the additional administrative and performance overhead.
In SQL Server 2014, organizations can encrypt data while creating a backup. This is achieved by specifying the encryption algorithm and the Encryptor when you create the backup. Encrypted backups are supported whether you create the backup on-premises or with Windows Azure. In addition, encryption can be applied when you use the Back Up Database wizard, the Maintenance Plan wizard, or with Transact-SQL.
Creating a database master key
The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database.
The is_master_key_encrypted_by_server column of the sys.databases catalog view in master indicates whether the database master key is encrypted by the service master key.
Here, the first step in successfully encrypting a backup file is to create a database master key. The following Transact-SQL example creates a database master key and stores the complex password in the database.
- Creates a database master key.
- The key is encrypted using the password “<master key password<” USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘2.48h]zD>qJ~NfL_L6dh’;
Creating a backup certificate
In SQL Server, certificates are stored within the database in which they were created. That means that they are backed up together with the database. However, as a certificate might hold the “key” to a lot of your data, it is often advisable to have a separate backup.
Certificate backups also provide a simple way to move or copy a certificate from one database to another.
The next step here, is to create the backup certificate in the master database. The following Transact-SQL example creates a certificate named MyDBBackupEncryptCert.
CREATE CERTIFICATE MyDBBackupEncryptCert
WITH SUBJECT = ‘MyDB Backup Encryption Certificate’;
Encrypting the backup by using the Backup Database wizard in SQL Server Management Studio
The need to keep data secure grows dramatically. Along with data, you need to be sure your database backup files are also secured, especially those on a server file system. The data inside the native SQL Server backup files is stored as a plain text on the file system.
Follow these steps to encrypt a database by using the Backup Database wizard:
- In SQL Server Management Studio, expand Databases, and then select the user database, you want to back up. In this example, the database, named SalesForcast is used.
- Right-click the database, point to Tasks, and then click Back Up.
- On the General page, specify the backup options you would usually select, such as backup type, backup component, and destination. For this example, a full database backup will be conducted and the destination will be a disk.
- On the Backup Options page, set Encrypt Backup to true. Please note that the encryption option is available only when Back Up To A New Media set is selected in Media Options.
- Select an encryption algorithm to use for the encryption step, and provide a certificate or an asymmetric key from a list of existing certificates or asymmetric keys. For this example, AES 128 and the certificate MyDBBackupEncryptCert, which was created in the previous example, are selected, as shown in figure 3.14
FIGURE 3-14 Specifying the encryption type and certificate or an asymmetric key to encrypt a database backup.
Encrypting the backup by using Transact-SQL
Backing up the database using T-SQL setup requirements, are the same as when using SQL Server Management Studio (SSMS).
The following Transact-SQL statement would encrypt the backup based on the inputs specified in the preceding example:
BACKUP DATABASE [SalesForcast]
TO DISK = N’C:\Temp\db_backup_cxvirmpz.wtq.bak’
WITH FORMAT, INIT,
MEDIANAME = N’SalesForcastMediaSet’,
NAME = N’SalesForcast-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD,
ENCRYPTION(ALGORITHM = AES_128,
SERVER CERTIFICATE = [MyDBBackupEncryptCert]), STATS = 10
SQL Server Managed Backup to Windows Azure
Managed Backup is a new feature introduced in SQL Server 2014 working with Windows Azure. This feature allows to manage and automate SQL Server backups (from your on-premise or Azure, SQL Server instance), configurable by … script only (T-SQL or PowerShell)!
Microsoft recommends to use Managed Backup for Windows Azure virtual machines.
This topic describes how to enable SQL Server Managed Backup to Windows Azure with default settings at both the instance and database level.
SQL Server 2014 introduces SQL Server Managed Backup to Windows Azure to further save on storage and administration while achieving additional off-site data protection with the Windows Azure Blob Storage service. SQL Server Managed Backup to Windows Azure as a SQL Server feature that automates database backup and maintains the backups based on the retention period. When using this new functionality, there isn’t a need to manage backup policies, and the backup strategy measures database usage patterns to set the frequency of backups to Windows Azure. The main differentiators and benefits for SQL Server Managed Backup to Windows Azure compared with traditional backups to the cloud includes the following:
Currently automating backups for multiple databases requires developing a backup strategy, writing custom code, and scheduling backups.
‘SQL Server Managed Backup to Windows Azure’ can be configured at the database or at an instance level. If configured at the instance level, the settings are applicable to any database created thereafter.
Additional granular control can be achieved for each database backup because you can override default instance-level settings when creating policies at the database level. For example, setting the retention period at the database level allows you to override the default settings at the instance level.
Specifying the retention period, and SQL Server Managed Backup to Windows Azure determines the type and frequency of backups for a database and stores the backups on the Windows Azure Blob Storage service. The supported values are in the range of 1-30 days.
When a backup is configured to use encryption, you have additional security for the backed up data.
Enabling SQL Server Managed Backup to Windows Azure at the instance level
The system stored procedure smart_backup.set_instance_backup is used to enable SQL Server Managed Backup to Windows Azure and configure the default settings at the instance level. The value 1 must be specified for the @enable_backup parameter to enable backups and set the default configurations. As indicated earlier, once the settings have been configured at the instance level, the settings will be applied to all new databases created on this same instance. The following items need to be specified as well: retention period (from 1 to 30 days), the SQL credential used to authenticate to the Windows Azure storage account, and whether to use encryption.
The following Transact-SQL syntax can be used to enable SQL Server Managed Backup to Windows Azure at the instance level. A retention period of 30 days and AES 128 encryption has been specified.
Use msdb; Go
@storage_url = ‘https://mystorageaccount.blob.core.windows.net’ ,@retention_days=30
,@credential_name=’MyCredential’ ,@encryption_algorithm =’AES_128’ ,@encryptor_type= ‘Certificate’ ,@encryptor_name=’MyBackupCert’ ,@enable_backup=1;
Enabling SQL Server Managed Backup to Windows Azure for a database
The system stored procedure smart_admin.sp_set_db_backup is used to enable SQL Server Managed Backup to Windows Azure for a specific database. The following items need to be specified along with enabling the backup: the name of the database, the retention period from 1 to 30 days, the SQL credential is used to authenticate to the Windows Azure storage account, and whether to encrypt the backups.
The following Transact-SQL syntax can be used to enable SQL Server Managed Backup to Windows Azure at the database level. A retention period of 30 days and AES 256 encryption has been specified.
EXEC smart_admin.sp_set_db_backup @database_name=’TestDB’ ,@enable_backup=1,
,@storage_url = ‘https://mystorageaccount.blob.core.windows. ,@retention_days =30
,@credential_name =’MyCredential’ ,@encryption_algorithm =’AES_256’ ,@encryptor_type= ‘Certificate’ ,@encryptor_name=’MyBackupCert’